I have a form that lets people add new individuals to my database. I use the following BeforeUpdate code to be certain that they do not leave the "department_id" field blank.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(department_id) Then
MsgBox "You must select a Department.", vbExclamation, "AMS Message"
Combo60.SetFocus 'Go back to Department ComboBox field
Cancel = True 'Cancel saving the record
It works fine this way. (I cannot restrict this from table properties. It must be optional to the table and that is why I choose this route.)
There is a 'Cancel' button on my form in case a person starts entering data and wants to quit.
Problem: When you click the Cancel, the BeforeUpdate sub runs and my message about selecting a department appears. Clicking OK on the message does then close the form. I included a "DoCmd.CancelEvent" in the close form sub and that does prevent any data getting written to the table but it does not stop the BeforeUpdate sub.
Is there a command to stop a Private Sub from running?