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 - 17 Mar 2008 20:35 GMT
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

Signature
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.
> I'm firing off a report using a command button, but I want to make sure
> there
[quoted text clipped - 44 lines]
> DoCmd.Close acForm, "frmgendrpt", acSaveNo
> End Sub
George Nicholson - 17 Mar 2008 20:38 GMT
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

Signature
HTH,
George
> I'm firing off a report using a command button, but I want to make sure
> there
[quoted text clipped - 44 lines]
> DoCmd.Close acForm, "frmgendrpt", acSaveNo
> End Sub