Suppress MsgBox

[alex]

Suppress MsgBox

Hello,

Using Access ’03…

I have the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'code to see if someone is trying to save a record with no hours
If IsNull(Hours) Then
MsgBox "You are trying to save a record with NO Hours!" _
& vbNewLine & _
"" _
& vbNewLine & _
"Use the undo button to cancel this record."
DoCmd.CancelEvent
Me.Hours.SetFocus

Else: Exit Sub
End If

End Sub

When this code fires, I get two message boxes—my message, and a
message from Access saying: You canceled the previous operation.

I like to suppress the latter message box. Is this possible?

I did modify the code above and changed DoCmd.CancelEvent to Me.Undo…
this works well (no second msgbox), but as you know it wipes out
"everything."

I basically want the user to enter hours in the hours textbox or undo
the record.

I do have data integrity rules on the table (for the hours field), but
as you know, the error that Access provides is not very eloquent.

Thanks,
alex

 

[Stefan Hoffmann]

hi Alex,

I did modify the code above and changed DoCmd.CancelEvent to Me.Undo…
this works well (no second msgbox), but as you know it wipes out
"everything."

Instead of DoCmd.CancelEvent use the Cancel parameter:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = IsNull(Me![Hours])

If Cancel Then
MsgBox "You are trying to save a record with NO Hours!" & _
vbNewLine & vbNewLine & _
"Use the undo button to cancel this record."
Me.Hours.SetFocus
End If

End Sub


mfG
--> stefan <--

 

[alex]

hi Alex,

I did modify the code above and changed DoCmd.CancelEvent to Me.Undo…
this works well (no second msgbox), but as you know it wipes out
"everything."

Instead of DoCmd.CancelEvent use the Cancel parameter:

   Private Sub Form_BeforeUpdate(Cancel As Integer)

      Cancel = IsNull(Me![Hours])

      If Cancel Then
          MsgBox "You are trying to save a record with NO Hours!" & _
                 vbNewLine & vbNewLine & _
                 "Use the undo button to cancel this record."
          Me.Hours.SetFocus
      End If

  End Sub

mfG
--> stefan <--

Stefan, thanks; I learned a new piece of code...I now, however, get a
different second message box that says: No current record.
alex

 

[Stefan Hoffmann]

hi Alex,

Stefan, thanks; I learned a new piece of code...I now, however, get a
different second message box that says: No current record.
alex

Try the the same without the Me.Hours.SetFocus.


mfG
--> stefan <--

 

[alex]

hi Alex,


Try the the same without the Me.Hours.SetFocus.

mfG
--> stefan <--

No difference...
As a note, our code above is in a subform...not sure if that makes a
difference.

 

[Stefan Hoffmann]

hi Alex,

No difference...
As a note, our code above is in a subform...not sure if that makes a
difference.

Does the subform contain any data?

You may try this:

If Me.Dirty Then
Cancel = IsNull...
If Cancel..
End If
End If


mfG
--> stefan <--

 

[alex]

hi Alex,


Does the subform contain any data?

You may try this:

   If Me.Dirty Then
     Cancel = IsNull...
     If Cancel..
     End If
   End If

mfG
--> stefan <--

Yes, there's data in the subform...that's why Me.Undo worked so well
(no second message)!
What I'll try (using your help above) is to modify the subform's code
such that a user cannot move past the [Hours] textbox if the value is
Null or "". That way the subform will not contain any data should a
user try to save a record with no Hours.
Thanks for all your help.
alex

 

[Stefan Hoffmann]

hi Alex,

Yes, there's data in the subform...that's why Me.Undo worked so well
(no second message)!
What I'll try (using your help above) is to modify the subform's code
such that a user cannot move past the [Hours] textbox if the value is
Null or "". That way the subform will not contain any data should a
user try to save a record with no Hours.

Try the code above in a new form/subform. I'm not sure if it is the
default behaviour or your current setup.


mfg
--> stefan <--