Has No Data and Msgbox --How to coordinate?

[LakotaMan]

Hi all,

Using Version 2002:
I’m having a small problem with nodata in a report. Here’s the background:

I am using a form as a “Build A Custom Report” screen to capture several parameters that I use in a SQL statement to open the report. As long as everything has data, it’s fine. When there’s no data for a certain combination of parameters, I get the usual “Error!” display in the textboxes.

I would like to have a messagebox come up, informing the user there is no data for the report and then taking them back to the form. I tried coding the msgbox into the NoData event of the report, with a Cancel=True line, and that part worked fine, but then the code broke back to the line where the report is being opened on the form with the following message: “The OpenReport action was canceled” which I understand, I just want to avoid. How to do it?

Here’s the last few lines of code:


If Len(Me.cboLocation & "") > 0 Then
strWhere = strWhere & " AND txtLocation = '" & Me.cboLocation & "'"
End If

If Len(Me.cboObservType & "") > 0 Then
strWhere = strWhere & " AND ObservationType = '" & Me.cboObservType & "'"
End If


If Len(strWhere & "") = 0 Then
DoCmd.OpenReport "rptStatsAllPlant", acViewPreview

Else
DoCmd.OpenReport "rptStatsbyDept", acViewPreview, WhereCondition:=Mid(strWhere, 6)

End If


If no parameters are chosen in the form, the StatsAllPlant report opens. It’s when they start choosing parameters for the StatsbyDept report that the problem arises.

As always, any help will be greatly appreciated.

Tru

 

[CosmoKramer]

Tru,

Check out this FAQ written by garridon: faq703-1594

Hoc nomen meum verum non est.

 

[LakotaMan]

CosmoKramer. . .

Thanks so much. . . works like a charm!

I have marked that FAQ for further reference and apologize for missing it in my research.

Your help has saved me many headaches today!

Tru

 

[ITProg]

Is there a way to put this code in a module and call the module from the report event? I have many reports I would like to add this code to and thought it might be easier if I could reference one module.