• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

By-Pass a Private Sub "BeforeUpdate" Command

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
   
    End If

End Sub
----------
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?

Thank you.
Jim
0
JimK31
Asked:
JimK31
1 Solution
 
thegornieCommented:
Maybe have a global variable CancelClicked, default value of False.

Set CancelClicked = True in the OnClick for the Cancel button.

Then modify your If statement above to include

   If Not CancelClicked And IsNull(department_id) Then


Good luck,
theGornie
0
 
muzakCommented:
You could make a public dim CancelHit as boolean variable at the start of the VB Code and set it to False at form open.Then change code before update to


 If IsNull(department_id)  And Not CancelHit Then
        MsgBox "You must select a Department.", vbExclamation, "AMS Message"
        Combo60.SetFocus  'Go back to Department ComboBox field
        Cancel = True     'Cancel saving the record
   
   End If
0
 
muzakCommented:
Looks like someone was thinking the same thing as me :)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
thegornieCommented:
Great minds think alike, eh?  :)
0
 
shanesuebsahakarnCommented:
An alternative - in your Cancel button, issue an Undo command:

Me.Undo
DoCmd.Close acForm, Me.Name

This will undo any changes made to the form so it will cause the BeforeUpdate event not to fire.
0
 
JimK31Author Commented:
A thanks to all 3 of you. theGornie and Muzak both had the same thought and both worked. I wish I could share the points with each of you, but I chose shanesuebsahakarn's suggestion because it worked perfectly and is only one added line of code. (me.undo)

Thanks again.
Jim
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now