Code to require fields on Before_Update not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following code works fine except that if one or more of the fields
mentioned are null, the form still closes when the user clicks the OK button
in the message. Any idea why? Thanks.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If MsgBox("Do you want to save this log?", vbYesNo) = vbNo Then
Me.Undo
Cancel = False

Else

If IsNull(Me.Part__) Or IsNull(Me.FoundAreaCmb) Or
IsNull(Me.Defect_Quantity) Or IsNull(Me.Operator_ID) Or IsNull(Me.Sect) Or
IsNull(Me.Operation) Or IsNull(Me.Customer) Or IsNull(Me.Cause) Or
IsNull(Me.Inspector) Or IsNull(Me.Action) Then
MsgBox "There is information missing in one or more of the required
fields.", vbOKOnly

Cancel = True

End If
End If

End Sub
 
I'm guessing that you're using a command button to close your form, rather
than clicking on the native Access close control, the big (X) in the upper
right hand corner. When you use the Command Button Wizard to generate a form
close button, it uses the command

DoCmd.Close

The problem with this is that when Access closes using this command, it dumps
any record that fails a validation (i.e. is empty, in your case) and gives
you no warning! The trick here, when using DoCmd.Close, is to precede it with
the line

If Me.Dirty Then Me.Dirty = False

This will force Access to go thru your validation routine.
 
Back
Top