By-Pass a Private Sub "BeforeUpdate" Command

Posted on 2003-03-11
Medium Priority
Last Modified: 2008-03-06
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.
Question by:JimK31
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8115501
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,

Expert Comment

ID: 8115510
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

Expert Comment

ID: 8115513
Looks like someone was thinking the same thing as me :)
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 8115532
Great minds think alike, eh?  :)
LVL 41

Accepted Solution

shanesuebsahakarn earned 500 total points
ID: 8115593
An alternative - in your Cancel button, issue an Undo command:

DoCmd.Close acForm, Me.Name

This will undo any changes made to the form so it will cause the BeforeUpdate event not to fire.

Author Comment

ID: 8116851
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.

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question