cancel print option if over x pages

[hardyd44]

Hi,

searched everywhere for this one - no joy

I can count the nember of pages in a report (using report header on format) however I would like to give the user an option to cancel the print if above x pages - the report if from a form with various combo boxes filtering fields changing the underlying query result however if the filters are not selected the report could be up to 300 plus pages - what i want to do in VBA is flag the number of pages to the user then allow them to cancel if required

any ideas????

thanks

Dean

 

[James Dudden]

Do you want to flag this before the report is opened if so the following may help?

If each line in your report (i.e. record) is of a similar size then you could use the recordcount (of the recordsource) as an estimate.

To do this you would have to work out how many records on average are to a report page and then you can work out how many per 10 pages.

Then in VBA when the user clicks the report button write code to return the number of records the report would produce based on the filters and then you can flag if necessary.

 

[hardyd44]

Hi, thanks for that - did look at that method but the amount of data in each record can alter resulting in more or less pages - will keep looking if no solution found, think that will have to be the best i can use

cheers
Dean

 

[stopher]

You can add the following code to the On Page event for your report:

Dim response As Variant

If Me.Page = 1 And Me.Pages > 2 Then
    response = MsgBox("number of pages = " & Me.Pages & " Do you wish to continue?", vbYesNo)
    If response = vbNo Then
        DoCmd.Close
    End If
End If

I think you will also need to add a textbox to your report with the control source (which forces the pages variable to be calculated):
=Pages

hth
Chris

 

[hardyd44]

Chris,

thank you - that worked like a charm, sorry for delay in replying had to put the project on the back burner for a couple of weeks whilst something else more urgent came up

again thanks

Regards

Dean