1
votes

I have a report in Access 2013 which groups the records from a query and prints each group to a separate page. Is there a way in VBA of finding out on which page each of the records will get printed?

EDIT - added these details from the comments:
The report will be anything from 1-5 pages. We were going to include the page number in the reference which gets printed on each page, e.g. "CN001-x" where the x would be the page number. We would then store that value back in a table in the database.

The report applies grouping to the data and creates a new page after every group and each page will have a variable number of records.

1
There are a lot of variables that could change/dictate what page a record can end up on. How many pages is the report normally? Could you provide the Query with the Grouping? And Finally, why do you need to know which page a record lands on? All of this can help me determine an appropriate solution.Pants
The report will be anything from 1-5 pages. We were going to include the page number in the reference which gets printed on each page, e.g. "CN001-x" where the x would be the page number. We would then store that value back in a table in the database.ChipsLetten
Is Each record the same height every time? Better way to ask would be, is the height of the first record going to be as tall as the 23rd record on your report?Pants
Yes they will be the same height.ChipsLetten

1 Answers

0
votes

By using a complex formula using Count(), you could calculate what page a record would land on. For instance, if twenty records fit on the first page, you would know that the 21st record would be on the second page. Since i cannot see your report or code, ill have to explain it.

Create a formula that counts the records in the report, Then use that formula in a comparison to see if the number falls in the 1-20 range. If so, assign it the value 1. Then just simply concatenate that field with the other part of your field that you need and you should be all good to go. Just do something like the below for the number of iterations that you will think you will need. If you know that you page count will never go above 7 than i would stop there.

IF Count(Field1) <= 20 THEN 
    1 
ELSE IF Count(Field1) > 20 AND Count(Field1) <=40 THEN 
    2 
Else 3  
End IF