Form Dialog Based on Query - Msg if No Match

[dar149]

I have a dialog form that pulls up a report. The dialog form is based on a query and uses two combo boxes to filter the records pulled into the report. I would like to have a message box display a message if there are no records that match the criteria. Now all it displays is the report with #Error where the formula results would be displayed.

Thanks...

 

[MarkWaddington]

Easy.

Go to the design view of the report, then go to your properties and in the "On No Data" event handler add the following:

MsgBox "There is no data for this report."
Cancel = -1

Hope this helps.

Mark Waddington.

 

[MarkWaddington]

Sorry, I meant in the VB code for the "on no data" event.. i.e. click the 3 dots that appear next to that field and click "code".

 

[dar149]

Thanks, that worked on the report. However, because I cancel the report, I get an "Open report action was canceled" error. My on-click event on the dialog form is as follows:

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String
stDocName = "rptPartNumberReport"
DoCmd.OpenReport stDocName, acPreview

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub

The error says "You used a method of the DoCmd object to carry out an action in VB, but then clicked Cancel in a dialog box."

Thanks...

 

[MarkWaddington]

There's probably a better way round this, but what I would do in this situation is find out the error number (by changing MsgBox Err.Description to MsgBox Err.Number - then running the report again).

Once you have the error number add a bit of code in the Err_Command34_Click section something like this:

If Err.Number = (whatever the error number is) Then

Response = Cancel

End If