msgbox pops up twice...how to stop it ?

[Pierkes]

Hi,

I have a quastion about a msgbox that pops up twice instead of just one time.

Here's the code, which is in the beforeupdate event of a form;

Private Sub Form_beforeupdate(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "vereist" Then
If LenB(Nz(ctl, vbNullString)) = 0 Then
MsgBox ("Vul iets in voor " & ctl.Name & "!"), , "Melding incomplete gegevens"
ctl.SetFocus
Cancel = True
Exit Sub
End If
End If
Next ctl
If Me![tr_result] <> "Open" And IsNull(Me![tr_reden]) Then
MsgBox ("Vul aub een reden in waarom iets niet door is gegaan!"), , "Melding incomplete gegevens"
Cancel = True
End If
End Sub

On the form there is a button which has the following code;

Private Sub cmdSaveandNew_Click()
On Error GoTo cmdSaveandNew_Click_Err
On Error Resume Next

If (Form.Dirty) Then
DoCmd.RunCommand acCmdSaveRecord
End If
    
If (MacroError.Number <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
Exit Sub
End If
    
On Error GoTo cmdSaveandNew_Click_Exit
DoCmd.GoToRecord , "", acNewRec
DoCmd.GoToControl "Naam relatie"

cmdSaveandNew_Click_Exit:
    Exit Sub

cmdSaveandNew_Click_Err:
    'MsgBox Error$
    Resume cmdSaveandNew_Click_Exit
End Sub

The only way to close the form is by pressing the button.
When i press the butto, i nicely get the fields which the user failed to fill out.
However when i discard of that messagebox, it pops up again. When i then discard it, it does not pop up anymore, untill i press the butto again, which will trigger the events again.

Can anyone please help me so that the messagebox appears only once ?

Thank you very much !
Pierkes

[MikeTheBike]

Hi

I must lead a sheltwed life, never come across MacroError before now, hence not sure what some of the code does, but I suggest changing this

DoCmd.GoToRecord , "", acNewRec 
DoCmd.GoToControl "Naam relatie"

to this

If Not Me.dirty then
    DoCmd.GoToRecord , "", acNewRec 
    DoCmd.GoToControl "Naam relatie"
End if

I believe moving to a new record will trigger the BeforeUdate event again as it is still Dirty (not saved because Update was cancelled).

I can't help feeling there is a better way of doing this!?

[Pierkes]

Hi mike,

Your solution works like a charm !
Thank you so much for the quick response AND coorect answer !

Best regards,
Pierkes

[zmbd]

If (MacroError.Num ber <> 0) Then
You should not be using this code for error checking for a VBA runtime error. Instead, you should be using If (Err.Number <> 0) Then for error checking.
Error Handling and Debugging Tips for Access 2007, VB, and VBA Office 2007 (however, the basics here apply to all versions)

Unless you have invoked a stored macro or this code has been triggered by a stored or embedded macro lines 9 thru 13 of the second code block will never execute.

Also in the second code block:
Line 3 voids what you set in line 2.
Line 15 is redundant to line 2; however, because of line 3 this may be why you need to insert it again.
Basically, remove line 2 or recode your error handling code to deal with the errors encountered at each stage of execution.

MacroError Object
Office 2007

Access Developer Reference
Represents the properties of a run-time error that occurs in a macro.

Remarks
>...snip...>
The MacroError object does not contain information about run-time errors that occur when running Visual Basic for Applications (VBA) code. See Elements of Run-Time Error Handling for more information about handling run-time errors in VBA.