Prevent unwanted report printing via VBA

[rcook2sc]

I am looking for a VBA method of checking with a user if they want to print a
report that contains over a certain number of pages (e.g. 20). The code I
have tried (in most OnPrint events!) is of the form:

If Pages > 20 Then
If MsgBox("The report contains " & Pages & " pages. Do you still
want to print it?", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then
'continue to print
Else
'cancel the print, closing the report if necessary
End If
End if

I tried DoCmd.Close acReport, Me.Name but ran into problems. I did read
GTajos post on a similar issue but that code did not work for me. Any help
greatly appreciated.

Thanks

 

[Allen Browne]

You won't be able to guess the number of pages in a report in its Open
event, and you can't cancel it after that, so you will be looking for an
alternative.

How about checking the number of records in its source query before opening
the report? Would that give you a near-enough idea of whether you should
warn the user that it will be a big one?

Below is an example of code you could put into a command button on a form,
to open Report1 which is based on query qry4Report1, even if you want to use
a filter (such as where SomeField contains your alias name), and warning if
there's more than 1000 records:

Dim strWhere as String
Dim bCancel As boolean

strWhere = "SomeField = 'rcok2sc'"
If DCount("*", "qry4Report1", strWhere) > 1000 Then
If MsgBox("Lotsa pages! Print anyway?", vbYesNo+vbDefaultButton2) <>
vbYes Then
bCancel = True
End If
End If
If Not bCancel Then
DoCmd.OpenReport "Report1", acviewPreview, , strWhere
End If