No current record/DoMenuItem cancelled

[Stephanie]

Hi. I'm trying to streamline my not-so-pretty code, and
actully make it work. I'm checking to make sure that
certain fields are completed before updating the record.
Here, I've shown 2 conditions I'm checking for (although
I have about 4 in reality). Before I added "Exit Sub" I
was getting prompted for both conditions (if appropriate)
without being able to fix the first condition. Then I
got an Access msg box: No current record.

After I added the Exit Sub(s), only the first condition
prompt occurs (yah!) but then I get an Access msg box:
DoMenuItem cancelled. My coding skills are very limited
and I can't figure out how to get one prompt as
appropriate. I appreciate any insight. Thanks, Stephanie

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim iAns As Integer
If (Me!Option1) = 0 And (Me!Option2) = 0 Then
iAns = MsgBox("Please select either 'ITA Member'
or 'R.E.A.D. Team' to continue, or click Cancel to erase
this record", vbOKCancel)
If iAns = vbOK Then
Cancel = True
Me!Option1.SetFocus
Else
Cancel = True
Me.Undo ' erase the entire form
End If
Exit Sub
End If
If IsNull(Me!MemberDateName) And (Not IsNull(Option1) Or
Not IsNull(Option2)) Then
iAns = MsgBox("Please enter an 'Associated Since' Date
to continue, or click Cancel to erase this record",
vbOKCancel)
If iAns = vbOK Then
Cancel = True
Me!MemberDateName.SetFocus
Else
Cancel = True
Me.Undo ' erase the entire form
End If
Exit Sub
End If
End Sub

 

[Allen Browne]

Hi Stephanie

This is needed in nearly every form, so here's what I do. The procedure
checks all the required conditions, and concatenates any messages it finds
so the user gets to hear about everything that is wrong. If nothing is
wrong, it builds up any warning messages that you might want to let the user
override. Finally, it calls the CancelOrWarn() to actually get the user
response. This procedure which lives into a general module so it's available
from all forms.

HTH.

---------------code starts----------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
Dim bWarn As Boolean 'Flag to warn user.
Dim strMsg As String 'MsgBox message.
'**************************************
'Handle required fields.
'**************************************
If (Me!Option1) = 0 And (Me!Option2) = 0 Then
Cancel = True
strMsg = strMsg & "Please select either 'ITA Member' or 'R.E.A.D.
Team'" & vbCrLf
End If
'Concatenate other messages the same way.

If Not Cancel Then
'**************************************
'Warning fields
'**************************************
If Me.BirthDate > Date() Then
bWarn = True
strMsg = strMsg & "Born in the future?" & vbCrLf
End If
'etc for other warnings.
End If

'Response
Call CancelOrWarn(Cancel, bWarn, strMsg)
End Sub
---------------------------------
Public Sub CancelOrWarn(Cancel As Integer, bWarn As Boolean, strMsg As
String)
On Error GoTo Err_CancelOrWarn
'Purpose: Display the message passed in if Cancel or bWarn is true.
'Arguments: Cancel = the argument to say the event is cancelled.
' bWarn = flag to warn use and ask whether to cancel.
' strMsg = the message to display to the user.
'Note: Cancel is changed to True if the user heeds the warning.
'Usage: In a BeforeUpdate event (e.g. of a form):
' Call CancelOrWarn(Cancel, False, "You can't do that!")
If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid data."
ElseIf bWarn Then
strMsg = strMsg & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2 + vbExclamation, "Are
you sure?") <> vbYes Then
Cancel = True
End If
End If
End Sub
---------------code starts----------------------

 

[Stephanie]

Allen, Thank you so much! Please hang in there with me
while I try to get it going.
First question: I made a new module to house the public
sub and name it CancelOrWarn.

When I use my form that should activate the code, I get
an error message- Complie error: Expected variable or
procedure, not module. I added "option explict" to the
module. But mostly I'm guessing. What have I done
wrong? Where do I store public subs? Thanks, Stephanie

 

[Stephanie]

Allen, I also am getting an error message for
On Error GoTo Err_Form_BeforeUpdate
Compile error: Label not defined.
Thanks, Stephanie

 

[Marshall Barton]

Allen, Thank you so much! Please hang in there with me
while I try to get it going.
First question: I made a new module to house the public
sub and name it CancelOrWarn.

When I use my form that should activate the code, I get
an error message- Complie error: Expected variable or
procedure, not module. I added "option explict" to the
module. But mostly I'm guessing. What have I done
wrong? Where do I store public subs?

To save you some time waiting for Allen to get back to you,
change the name of the module to something other than the
name of a procedure. Most people add either bas (old style)
or mdl to the module name to make sure it doesn't conflict
with a procedure name.

 

[Stephanie]

Thanks Marsh. That and commenting out the "on error"
statements in both the private sub and public sub allowed
me to make sure that my conditions get triggered. I'll
just wait to find out how to fix the "on error"
portions. Thanks so much! Stephanie

 

[Allen Browne]

Sorry, Stephanie. I deleted the error handler from the end of the procedure,
but left the label at the top.

Commenting it out is correct, unless you add the error handler at the
bottom. The one we actually use logs the error messages, because users never
write them down for you. If you want to copy ours, it's available in this
link:
Error Handling in VBA
at:
http://members.iinet.net.au/~allenbrowne/ser-23a.html

 

[Stephanie]

Thanks for the help- I really appreciate you and the other
fabulous MVPs that help us (forever)newbies! Stephanie