command button/case statement issue

[Mark]

Hi all,

I have a form, which has a command button that allows the
user select from 3 options from a message box. The
options are to either: 1. "Save" the information that they
typed into the form; 2. "Not save" the information; 3.
Return to the form. As in: MsgBox("Do you want to save
the information entered? Select 'Yes' to save record.
Select 'No' to exit and not save record. Select 'Cancel'
to return to same record.",

My problem and question is: I would like to add code to
command button to stop the "Case" statement if certain
fields are Null/blank. As in:

If Me.ConsultFName = Null Then
MsgBox "Please fill in the consultant's first name
before saving record.", vbInformation, "Notice:"
Me.ConsultFName.SetFocus

ElseIf Me.ConsultLName = Null Then
MsgBox "Please fill in the consultant's last name
before saving record.", vbInformation, "Notice:"
Me.ConsultLName.SetFocus

I've tried a few things, but cannot seem to figure out how
to do this. One thing I tried was to add a second Case
Statement like:

intAnswer2 = MsgBox("Please confirm saving record at
this time.", vbOKCancel + vbExclamation, "Please respond")
Select Case intAnswer2

..and put in the If statement code from above to prompt the
user if these fields were not filled in, but it is not
working correctly.

Any help would be greatly appreciated!!!!!! Below is the
command button code:



Private Sub cmdAddInfo_Click()
On Error GoTo Err_cmdAddInfo_Click

Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to save the
information entered? Select 'Yes' to save record.
Select 'No' to exit and not save record. Select 'Cancel'
to return to same record.", vbYesNoCancel +
vbQuestion, "Please respond")


Select Case intAnswer

'Yes
Case vbYes

cmdAddInfo.Caption = "Saved"
ExitForm.Visible = True
ExitForm.SetFocus
cmdAddInfo.Enabled = False
DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70


'Locks all records after clicking the "cmdAddInfo"
command button, so no additional records can be added'
Me.ConsultIDNumber.Locked = True
Me.ConsultIDNumber.Enabled = False
Me.ConsultIDNumber.SpecialEffect = Flat
Me.ConsultIDNumber.BorderStyle = Transparent

Me.ConsultFName.Locked = True
Me.ConsultFName.Enabled = False
Me.ConsultFName.SpecialEffect = Flat
Me.ConsultFName.BorderStyle = Transparent

Me.ConsultLName.Locked = True
Me.ConsultLName.Enabled = False
Me.ConsultLName.SpecialEffect = Flat
Me.ConsultLName.BorderStyle = Transparent



'No
Case vbNo
Me.ConsultFName = "Invalid entry"
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,
acMenuVer70
DoCmd.Close

'Cancel
Case vbCancel
Me.ConsultIDNumber.SetFocus

End Select

Exit_cmdAddInfo_Click:
Exit Sub

Err_cmdAddInfo_Click:
MsgBox Err.Description
Resume Exit_cmdAddInfo_Click

End Sub

 

[M.L. Sco Scofield]

Try putting in:

Goto Exit_cmdAddInfo_Click

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com