canceling a BeforeUpdate event (A97 SR2)

In the form I'm currently working on, the BeforeUpdate event calls a procedure which validates most of the text & combo boxes on the form. If any of the required ones are blank, it cancels the BeforeUpdate event and returns to the form with the fields in question highlighted.

I find that if the BeforeUpdate event was triggered by tabbing out of the last field on the form, it works fine. If the BeforeUpdate was triggered by clicking on a "save record" command button, however, it pops up a dialog box with a message that I think is both unnecessary and misleading to the user. (The box says "The DoMenuItem action was canceled. You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box. [etc.]" Since there was no Cancel button clicked, I think this is likely to lead to confusion for anyone else who uses the database.)

Is there a way to have this happen more gracefully, with the only dialog box that appears being the one I coded using MsgBox?

This is the code in my BeforeUpdate event procedure, based on some that Jayden posted in post #90321 in the "trapping form errors" thread (thanks, Jayden):

<pre>Private Sub Form_BeforeUpdate(Cancel As Integer)
'Check that all required fields have data and return user to form if not.

Dim currCtl As Control
Dim blnFieldLeftBlank As Boolean

blnFieldLeftBlank = False

For Each currCtl In Me.Controls
If Not (currCtl.Tag = "BlankOK") Then
If IsNull(currCtl) Then
blnFieldLeftBlank = True
currCtl.BackColor = 6619135
Else
currCtl.BackColor = 15138815
End If
End If
Next currCtl

If blnFieldLeftBlank Then
MsgBox "At least one required field has been left blank." & _
vbCrLf & "These fields are highlighted.", vbInformation, _
"Required data not entered"
Cancel = True
End If

End Sub
</pre>

The procedure for the Save button's Click event is the basic one generated by the Command Button Wizard:

Any suggestions? I'm beginning to feel as if I've been going in circles among the same few topics in the online help, and if any of the books I own include this info, I must be looking in the wrong place. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

Re: canceling a BeforeUpdate event (A97 SR2)

I'm probably being too naive here, but what do you need a save record button? Is it just user comfort that the record won't be saved unless you have one? What happens here is that when the Save Record button is clicked, Access tries to save the record in VBA module cmdSaveRecord, which fires the event Form_BeforeUpdate. A validation error then causes you to do the Cancel command, and things get confused. If your users really need a Save Record button, make it sort of a dummy one that really trys to goto the next record, or some other action that would cause an implicit save. Charolette or Rory may have other ideas about how to solve this dilema, but we don't normally give users a Save Record button, and just let Access do it's thing. Let's see what other folks suggest.

Re: canceling a BeforeUpdate event (A97 SR2)

The reason for the save record button is that I'm hoping to give the user as few navigation options as possible. The form will be used for data entry only, and I'd like to give only two choices: save record and create a new one, or undo the data that's been entered and return the form to its original state.

I also tried creating a command button to add a new record (since that automatically saves the open one, right?) and got a different error message after my validation procedure: "You can't go to the specified record. You may be at the end of a recordset." Is this because the BeforeUpdate event of the open record was canceled?

Re: canceling a BeforeUpdate event (A97 SR2)

Sorry - I was a little slow thinking on this! I think your code should be running on the Before Insert event, not on the Before Update event. You can't do an update on a record until it has been saved. Try changing your code to run on the insert event, and see if that doesn't make things behave better. <img src=/S/frown.gif border=0 alt=frown width=15 height=15>Sorry for not thinking of it earlier.

Re: canceling a BeforeUpdate event (A97 SR2)

Hmmm... mixed results so far. When I moved the code to the BeforeInsert event, it tried to validate the required fields when I typed the first character in the new record. Naturally, it flagged them all. However, it did return to the form without showing the "you clicked cancel" dialog box.

I think I'm confused about the sequence of events. BeforeInsert happens too early, before the user has entered any data. BeforeUpdate results in the unwanted dialog box. AfterUpdate and AfterInsert happen too late and can't be canceled. Surely there must be a way to do this kind of validation on a newly-entered record?
<img src=/S/confused.gif border=0 alt=confused width=15 height=20>

Re: canceling a BeforeUpdate event (A97 SR2)

BeforeInsert happens as soon as the user types in a single character and before a new record is inserted into the recordset to hold the data. You're getting a message because you cancelled the BeforeUpdate event, so your save event got stepped on. The graceful way to handle this is to change your save button code. First, add a module level variable, something like

Dim mblnValidated As Boolean

Then in your BeforeUpdate event, set the variable to true if everything validates and false if it doesn't. You'll also want to set the flag to True in the OnCurrent event of the form, which happens when you move to a new record, among other times. In your save button code, change it to this:

Re: canceling a BeforeUpdate event (A97 SR2)

Thank you Charlotte - I'm afraid my brain has been off track for two days now - fighting through a SQL 2000 upgrade that went awry. And my apologies to EmilyB for sending you off on the wrong track! <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Charlotte's advise is quite correct and should do exactly what you were trying to do.

Re: canceling a BeforeUpdate event (A97 SR2)

Hmm, we're going to be going through an Oracle upgrade in January/February next year so perhaps I should warn everyone now to ignore anything I post during those months! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Mind you, they should probably ignore me anyway....

Re: canceling a BeforeUpdate event (A97 SR2)

I have a very simple solution to that "The DoMenuItem action was cancelled...." message. Since I have never come up with circumstances where I would consider that a legitimate error, I always ignore error number 2501 in all my error handling code. That keeps the silly message from appearing.

Re: canceling a BeforeUpdate event (A97 SR2)

Charlotte - I made the changes you suggested, and now I find that it will validate the fields the first time I click the button, but won't on subsequent clicks (even if I've added or changed data in the fields). I think mblnValidated is still set to False so it doesn't try to save the record, and hence doesn't trigger the BeforeUpdate event. Could this be because returning to the form from the MsgBox (called in the validation procedure) doesn't trigger the Current event?

I think I need to put another "mblnValidated = True" in the code somewhere. Changing the code like this seems to work:
<pre>Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

If mblnValidated Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If

Are there any hidden dangers to putting something in the Exit part of the sub?

Commenting out the "MsgBox Err.Description" line solved the problem of it displaying the message box with the error description. Funny how that works...
<img src=/S/doh.gif border=0 alt=doh width=15 height=15>

Many thanks for the suggestions. Douglas, thanks for the idea on the error code. As a novice, I hadn't been thinking about being able to trap a particular error. I think that will come in handy in the future.

Re: canceling a BeforeUpdate event (A97 SR2)

Emily,

Where you put the line resetting the variable depends on what you want to accomplish. Your code won't save the record unless the variable is set to True. You appear to be setting it to True in the cmdSaveRecord_Click event even if the validation has *not* set it to true. I didn't think that's what you wanted to do.

Your BeforeUpdate event is where the variable should be set to True or False, and you haven't posted your revisions to that routine, so I can't really say what you should be doing.

Re: canceling a BeforeUpdate event (A97 SR2)

There's definitely something I'm unclear about here, and I think it may have to do with the Current event and what triggers it. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> If I explain my understanding of the sequence of events, perhaps one of you experts will spot the problem.

1. The form is opened on the screen, which triggers the Current event:
<pre>Private Sub Form_Current()
mblnValidated = True
End Sub
</pre>

2. The user enters data in the fields. For the sake of discussion, assume they leave at least one required field empty.

If mblnValidated Then
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Changed this from simply saving it to saving & creating a new record:
DoCmd.GoToRecord , , acNewRec
End If

4. When the "DoCmd.GoToRecord , , acNewRec" line is executed, it triggers the form's BeforeUpdate event procedure:
<pre>Private Sub Form_BeforeUpdate(Cancel As Integer)
'Check that all required fields have data and return user to form if not

Dim currCtl As Control
Dim blnFieldLeftBlank As Boolean

blnFieldLeftBlank = False

For Each currCtl In Me.Controls
If Not (currCtl.Tag = "BlankOK") Then
If IsNull(currCtl) Then
blnFieldLeftBlank = True
currCtl.BackColor = 6619135
Else
currCtl.BackColor = 15138815
End If
End If
Next currCtl

If any of the required fields are blank, this procedure sets mblnValidated = False and issues a DoCmd.CancelEvent. The DoCmd.CancelEvent cancels the BeforeUpdate event, which prevents the "DoCmd.GoToRecord , , acNewRec" from executing.

5. The user is returned to the form with the required fields highlighted. Because the focus has not moved from one record to another, and because the form has been neither refreshed nor requeried, the Current event does not take place. As a result, mblnValidated = False (still).

6. The next time the user clicks the save button, it runs the cmdSaveRecord_Click procedure. However, since mblnValidated = False, it doesn't execute the "DoCmd.GoToRecord , , acNewRec" command. As a result, the BeforeUpdate event is not triggered, the fields are not validated, and mblnValidated doesn't get set to match the true, current state of the required fields.

What am I missing? Is there a way to force the Current event to take place again? If not, I can't see where to reset mblnValidated = True so the validation routine in BeforeUpdate runs, if not in the Exit_cmdSaveRecord_Click part where it's indicated.

Any suggestions? Enlightenment? Recommendations for good Access books so I can go RTM some more on my own time? <img src=/S/rtfm.gif border=0 alt=rtfm width=24 height=23>

Re: canceling a BeforeUpdate event (A97 SR2)

Take the validation code out of the BeforeUpdate event and put it in a separate sub. Call it from the BeforeUpdate event and use the value in the boolean variable to determine whether to set Cancel = Not mblnValidated. In the Current event of the form, you can go ahead and set the variable to True if you want to. In the Click event of the button, test for the Dirty condition of the form *AND* the boolean variable. There is no reason to issue a DoCmd.RunCommand acCmdSaveRecord (which is the current equivalent of your Access 95 wizard code--don't ask me why the wizards write archaic code) unless there's something to save. Your validation code should set the boolean variable to True if there are no missing values .