How to stop an MSaccess form from closing when validating field

blink496

Hi,
I've got a close button on my form as follows:
Private Sub exitForm_Click()
DoCmd.Close
End Sub

In the beforeupdate event of the form I have have following code to validate the forms fields:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Place an asterisk (*) in the Tag Property of the text boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:


Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control


nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next
End Sub

This validation works fine up to a point. If I try to close the form and a required field is empty, I get a message box asking me to fill in the data. The problem is that when I click ok to the message, it shuts down the form, so I have to re-enter all the data again. My question is how do I prevent the form from closing after clicking ok on the message box?

appreciate your help:)

Gosh

Your DoCmd.Close is firing the BeforeUpdate event which when it finds a textbox is empty puts out a message and then sets Cancel to true (which prevents the update) and then returns to the DoCmd.Close which closes the form.

You could do the following - this will not do the DoCmd.Close if the Validate_Form sets Cancel to true

Private Sub exitForm_Click()
   Call Validate_Form (Cancel)
   If Cancel then
       Exit Sub
   End If

   DoCmd.Close
End Sub


Private Sub Validate_Form(Cancel As Integer)

'Place an asterisk (*) in the Tag Property of the text boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:


    Dim msg As String, Style As Integer, Title As String
    Dim nl As String, ctl As Control

Cancel = False
    nl = vbNewLine & vbNewLine

    For Each ctl In Me.Controls
       If ctl.ControlType = acTextBox Then
          If ctl.Tag = "*" And Trim(ctl & "") = "" Then
             msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
                   "You can't save this record until this data is provided!" & nl & _
                   "Enter the data and try again . . . "
             Style = vbCritical + vbOKOnly
             Title = "Required Data..."
              MsgBox msg, Style, Title
             ctl.SetFocus
             Cancel = True
             Exit For
         End If
      End If
    Next
End Sub

blink496

Thanks Gosh,
sounds good, but it didn't quite work:) . I changed the code slightly and now I'm almost there. When a field hasnt been filled, it works fine. the message appears and when I click on ok, the form stays open. But now, when all the fields are entered correctly, the close button doesnt work. Its something to do with the way the parameters are passed between the 2 subs, but I'm at a loss to figure it :

Private Sub exitForm_Click()

Call Validate_Form((Cancel))
   If Cancel = False Then
   
      Exit Sub
      
   End If
    DoCmd.Close

End Sub
Private Sub Validate_Form(Cancel As Integer)

'Place an asterisk (*) in the Tag Property of the text boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:


    Dim msg As String, Style As Integer, Title As String
    Dim nl As String, ctl As Control


    nl = vbNewLine & vbNewLine

    For Each ctl In Me.Controls
       If ctl.ControlType = acTextBox Then
          If ctl.Tag = "*" And Trim(ctl & "") = "" Then
             msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
                   "You can't save this record until this data is provided!" & nl & _
                   "Enter the data and try again . . . "
             Style = vbCritical + vbOKOnly
             Title = "Required Data..."
              MsgBox msg, Style, Title
             ctl.SetFocus
             Cancel = True
             Exit For
         End If
      End If
    Next
    
End Sub

Gosh

Try this - slight difference (in bold)

Private Sub exitForm_Click()

[B]Dim FormErrors as Boolean[/B]

Call Validate_Form([B]FormErrors[/B])

[B]' FormErrors will be True if Validate_Form found an error[/B]

   [B]If FormErrorsThen[/B]
   
      Exit Sub
      
   End If

   DoCmd.Close

End Sub
Private Sub Validate_Form([B]FormErrors As Boolean[/B])

'Place an asterisk (*) in the Tag Property of the text boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:


    Dim msg As String, Style As Integer, Title As String
    Dim nl As String, ctl As Control


    nl = vbNewLine & vbNewLine

    [B]FormErrors = False[/B]

    For Each ctl In Me.Controls
       If ctl.ControlType = acTextBox Then
          If ctl.Tag = "*" And Trim(ctl & "") = "" Then
             msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
                   "You can't save this record until this data is provided!" & nl & _
                   "Enter the data and try again . . . "
             Style = vbCritical + vbOKOnly
             Title = "Required Data..."
              MsgBox msg, Style, Title
             ctl.SetFocus
             [B]FormErrors [/B]= True
             Exit For
         End If
      End If
    Next
    
End Sub

blink496

thanks Gosh....its working now