Check the number of pages in a report prior to printing

[tsison7]

I have several reports that may end up being 50+ pages. I'd like a warning
to show up if any report is larger than 10 pages. That way if the user
didn't mean to publish all of the pages he can cancel out of the print job.

What is the best way to do this? I'd like a single Public Function that I
can call up for any "Print" command button.

 

[John Spencer]

The only way I can see to handle this would be to force the report to
preview AND use the PAGES function to get the number of pages. Then you
could issue a warning if the Pages value exceeded a specified threshhold.



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jeff Boyce]

I'm with John on this, but I usually take it a step further.

I very rarely give the users a way to go directly to <Print>, and instead
force them through a <Print Preview> step. That way, they can decide they
only needed to see it, only needed to see the last page of it, didn't really
want that one, etc., or actually did need it, at which point they can send
it to the printer or output to Word or to Excel, ... This approach gives
them considerable flexibility with little to no programming needed.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Allen Browne]

John and Jeff have answered your question regarding the number of pages, but
you may be able to give the warning based on the number of records rather
than the number of pages.

For example, if a typical page fits (say) 5 records on it, you could warn if
there were likely to be more than 50 records like this:

Dim bCancel As Boolean
Dim lngCount As Long
Dim strMsg As String
Dim strWhere As String

lngCount = DCount("*", "Query1", strWhere)
If lngCount > 50 Then
strMsg "There's more than 50 of the critters to print." & vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo, "That many?") <> vbYes Then
bCancel = True
End If
End If
If Not bCancel Then
DoCmd.OpenReport "Report1", acViewPreview,, strWhere
End If

In this example:
- Query1 is the RecordSource for Report1;
- any filter you are applying is in strWhere.