Msgbox vbretry (get user to retry and fill in information)

[John]

I'm firing off a report using a command button, but I want to make sure there
are not any null values on my form. I have 3 combo boxes that all need
values in order to generate the report properly. I have setup my code as
follows, but it is not producing the results. The message boxes all appear,
but the event is not cancelled to allow the user to select the information;
therefore an error is created on the report because no info was selected.
Please let me know what I'm doing wrong:

On the "click property" of the command button I have as follows:

Private Sub Command11_Click()

If IsNull(cmbdept.Value) Then
Dim intResponse As Integer
intResponse = MsgBox("Please Select Department", vbRetryCancel, "Select
Department")
cmbdept.setfocus

Else

If IsNull(cmbpri1.Value) Then
Dim intResponse As Integer
intResponse = MsgBox("Please Select 1st Prority", vbRetryCancel, "Select
Starting Priority")
cmbpri1.setfocus

Else

If IsNull(cmbpri2.Value) Then
Dim intResponse As Integer
intResponse = MsgBox("Please Select 2nd Prority", vbRetryCancel, "Select
Ending Priority")
cmbpri2.setfocus

Else

End if
End if
End if

DoCmd.OpenReport "rptbypri", acViewPreview
DoCmd.Close acForm, "frmgendrpt", acSaveNo
End Sub

 

[John Spencer]

Private Sub Command11_Click()
Dim intResponse As Integer

If IsNull(cmbdept.Value) Then

intResponse = MsgBox("Please Select Department", vbRetryCancel, _
"Select Department")
cmbdept.setfocus

ElseIf IsNull(cmbpri1.Value) Then
intResponse = MsgBox("Please Select 1st Prority", vbRetryCancel, _
"Select Starting Priority")
cmbpri1.setfocus

ElseIf IsNull(cmbpri2.Value) Then
intResponse = MsgBox("Please Select 2nd Prority", vbRetryCancel
, "Select Ending Priority")
cmbpri2.setfocus

Else

DoCmd.OpenReport "rptbypri", acViewPreview
Me.Visible = False 'Hide the form. If the report is using the values
then
' you don't want to close it.

'You can close the form using the close event of the report.
' DoCmd.Close acForm, "frmgendrpt", acSaveNo
End If

End Sub


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[George Nicholson]

Two possible approaches to preventing the OpenReport command from executing:

1) (Add Exit Sub to each of your 3 conditions)
intResponse = yada yada
somControl.SetFocus
Exit Sub

Or:

2) Rely on intResponse being zero if no msgboxes were generated. If they
were, intResponse would be either 2 or 4 (for Cancel and Retry
respectively).

If intResponse = 0 Then
DoCmd.OpenReport "rptbypri", acViewPreview
DoCmd.Close acForm, "frmgendrpt", acSaveNo
End If