Data Validation & Cancel Exit Command

[Sue Wilkes]

I have a form with a command button which when clicked returns the users to
the main menu on the switchboard. I have tried to add data validation but
when the button is clicked it gives the error message then saves the record
and returns to the Main Menu. I have posted my code below could anybody
please tell me what I'm doing wrong. I would like them to be placed back on
the form to either complete and save the record as normal or cancel all
changes & then return to Main Menu. The error message for 'designation code'
does not appear in full any pointers on this would be gratefully appreciated.
Many thanks Sue

Private Sub ReturntoMainMenu_Click()
On Error GoTo Err_ReturntoMainMenu_Click

If IsNull(Me.Designation) Then
MsgBox conMESSAGE, vbExclamation, "CANCEL ALL CHANGES OR ENTER
DESIGNATION DETAILS & SAVE RECORD BEFORE EXITING TO MAIN MENU"
Canel = True
Me.Designation.SetFocus
End If
If IsNull(Me.SentTo) Then
MsgBox conMESSAGE, vbExclamation, "ENTER SENT TO DETAILS BEFORE
CONTINUING"
Canel = True
Me.SentTo.SetFocus
End If
If IsNull(Me.CopiedTo) Then
MsgBox conMESSAGE, vbExclamation, "ENTER COPIED TO DETAILS BEFORE
CONTINUING"
Canel = True
Me.CopiedTo.SetFocus
End If
If IsNull(Me.DateSent) Then
MsgBox conMESSAGE, vbExclamation, "ENTER DATE AS DD/MM/YYYY BEFORE
CONTINUING"
Canel = True
Me.DateSent.SetFocus
End If
If IsNull(Me.CompanyNames) Then
MsgBox conMESSAGE, vbExclamation, "ENTER COMPANY NAME DETAILS BEFORE
CONTINUING"
Canel = True
Me.CompanyNames.SetFocus
End If
If IsNull(Me.Subject) Then
MsgBox conMESSAGE, vbExclamation, "ENTER SUBJECT DETAILS BEFORE
CONTINUING"
Canel = True
Me.Subject.SetFocus
End If
If IsNull(Me.Hyperlink1) Then
MsgBox conMESSAGE, vbExclamation, "ENTER HYPERLINK BEFORE CONTINUING"
Canel = True
Me.Hyperlink1.SetFocus
End If

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Switchboard"
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ReturntoMainMenu_Click:
Exit Sub

Err_ReturntoMainMenu_Click:
MsgBox Err.Description
Resume Exit_ReturntoMainMenu_Click

End Sub

 

[Keith Wilby]

I have a form with a command button which when clicked returns the users to
the main menu on the switchboard. I have tried to add data validation but
when the button is clicked it gives the error message then saves the
record
and returns to the Main Menu. I have posted my code below

<snip>

Sue,

It would be far simpler to make these fields "required" at table level but
if you must do it this way then I would recommend you look at putting the
code in the form's before update event ... a command button does not have
the "cancel" property you're trying to use.

Regards,
Keith.
www.keithwilby.com

 

[Ed Robichaud]

You need an "Else" condition. When your code checks for a control null, it
pops a msgbox, then sets focus to that control, then goes to the next
If..End IF, then goes to the next lines and closes the form.

What you want is to change all those subsequent IF statements to ESLEIF,
then and an ELSE before the "DoCmd.Close" line. That way, all the IF
conditions must be met before the form is closed.

-Ed

 

[Sue Wilkes]

Thank you Ed for your information it did achieve what I asked but I think I
approached it from the wrong angle. I have set the command button to undo
all changes and just return the user to the main menu. What I would like to
do is when the command button is clicked on a message to appear 'this will
cancel any changes on the current form and return to the main menu' but then
to give the option of yes or no. Yes goes to the main menu No to cancel the
undo and close commands and return them to the form. I have changed the
coding as below but cannot get it to work properly I would appreciate it if
you could point me in the right direction. Kind regards Sue

If Me.AddNewRec.Enabled Then
If MsgBox("THIS WILL CANCEL ANY CHANGES ON CURRENT FORM AND RETURN TO
MAIN MENU", vbQuestion + vbYesNo) = vbNo Then
stDocName = "Switchboard"
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
DoCmd.CancelEvent
End If
End If

 

[Ed Robichaud]

Again, be careful with the sequencing of commands. Try:

If Me.AddNewRec.Enabled Then
If MsgBox("THIS WILL CANCEL ANY CHANGES ON CURRENT FORM AND RETURN TO
MAIN MENU", vbQuestion + vbYesNo) = vbNo Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,
acMenuVer70
DoCmd.Close
Else
stDocName = "Switchboard"
DoCmd.CancelEvent
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If


As a favor to your users, you should probably alert them to missing required
data, before they get all the way through your form.
-Ed

 

[Sue Wilkes]

Many thanks Ed, I changed the code around a little to get the result I needed
but many thanks for sorting out the sequence for me. Regards Sue (Merry
Chirstmas!)