Hi
Can anyone tell me how I can prevent a user from closing a form unless certain criteria are met? To be more specific, I have a form called Journals. In order for the system to balance the sum of the journals entered must equal zero.
Any ideas would be most welcome.
thanks
liz

Go to the form's properties dialog box, select the Other tab (I think) and select Modal. This will prevent the user from doing ANYTHING in the application unless a condition is met on the form (Um, other than right clicking on the form, selecting Design View, and then closing the form that way. That will take additional code, but most users won't figure it out. If you have validation on your table fields, this will also prevent fruther action, or error or condition checking on your form fields.)

HTH



mmcdonal

Hi Liz,

The following will completely lock down a form for you. The user will not be able to close the form by using any of the following:

- Close box in upper right hand corner of form
- Control box in upper left hand corner of form
- Ctrl-F4

The only way to close the form will be by clicking a Close button with some data validation code behind it.

In my example, I just placed an unbound textbox (Text1) on the form, and a command button (cmdClose). The user can’t close the form unless 0 is entered in the textbox. The error handler traps for the “Close action cancelled” error and displays a message box instructing the user to enter 0 in Text1.

Option Compare Database
Option Explicit

Private blnCanClose As Boolean

Private Sub cmdClose_Click()
  On Error GoTo ErrHandler

  blnCanClose = False
  Me.Text1.SetFocus
  If Me.Text1.Text = CStr(0) Then
    blnCanClose = True
  End If
  DoCmd.Close

ExitProcedure:

    Exit Sub

ErrHandler:
    Dim strMsg As String
    If Err.Number = 2501 Then 'Close action cancelled
        strMsg = "Cannot close form" & vbCrLf & vbCrLf
        strMsg = strMsg & "Text1 must show 0."

        MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Data validation error"
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    End If

    Resume ExitProcedure

End Sub

Private Sub Form_Unload(Cancel As Integer)
  Cancel = Not blnCanClose
End Sub

HTH,

Bob

Hi Bob & mmcdonal
Thanks for your reply's. I have used Bob's code and it works like a dream
Regards
Liz