I am running a report from a parameter query. The query's recordset can vary from one to "n" records. Should the value of [Total] in any one of the records exceed a predetermined value, I would like a message box to appear informing the user that the report cannot be distributed until further review.

In the On Open event of the report, I referenced a macro to display a message box. The condition in the macro reads: [Reports]![ReportName]![Total] >100000. When the report tries to open, I receive the message: "Type Mismatch". The value of [Total] is calculated in the query that generates the report. I double-checked the query and the value is in fact a number.

Am I going about this incorrectly? Do I need a module that defines the recordset?

Thanks as always for allowing me to pick your brains.

- Matt

Matt,

Are you willing to use code rather than a macro? If so, create a query (call it, say, QryChkTotal) based on the query that feeds your report. QryChkTotal only needs 1 field: Totals. In the criteria row, type >100000.

Before opening the report, use Dcount to validate:

Dim intCount as integer

'will there be any items over 100000 in the report?
intCount = DCount("Total","QryChkTotal")

If intCount > 0 Then
Msgbox "Can't open this report, etc"
Else
Docmd.Openreport "rptXyz" ...
End if

Regards,
Tim

Tim -

Nice solution! One quick question. Your idea was to replace this in the 'On Click' event of my command button that opens the report? (I currently just have my docmd.openreport command in that event)

Thanks again Tim. For some reason, I never thought to simply count the number of records.

Matt,

That should work, yes -- putting the code behind the button that opens the report.

Regards,
Tim