Sorry to ask a computer question, but if I should ever get in trouble for posting at work, at least I can pull up this thread and show that some of my activity was relevant.
OK here is the situation - I need to run a report every day that is based on a query. This report shows the daily sales orders in a particular category (such as all priority shipments). The number of records could be anywhere from say 10 to 600. And actually, it isn’t me who will be printing them. I am building the application for another who is not at all computer savvy. She will click the button I give her on the form and it will print the daily report(s).
The issue: I want to print only 30 records at a time. After every 30 records, I want the page numbering to reset to 1 and I want the report header to print again. So I basically want the records divided into buckets.
I can’t figure out a good way to do this. The only thing I can think of is to do a top 30 on the query, then do an unmatched query to get the next 30 and so on. But the problem with doing it that way is the variablility of the number of lines. (It also means building several queries and several reports). I will have to guess the maximum number of records I will ever have, and then print blank reports if I don’t hit that maximum. That is really ugly and there must be a better way.
It sounds like you need to build a report based on the query. Using one of those you can control page headers and footers much more closely than with a simple query.
I HAVE created a report based on the query. You say I can control the headers easier that way. Can you tell me HOW to make the report reset itself after every 30 lines?
As far as printing the report header after every 30 records, you can do this by setting Group On for the detail (under Sorting and Grouping) to Interval, and Group Interval to 30.
To control the page number, you would have to write some VBA code, attached to the header’s Format event, to set Me.Page to 1.
(This is basically from memory, so I may have some of the details wrong, but it should at least point you in the right direction.)
I tried changing the “group on interval” for the detail level to 30, but nothing happened. Then I added a header identical to the report header for the detail level, but what ended up happening was that I got the report header before every record, not before every 30th record (I have several other headers, such as ship method and order number at higher levels of detail that I can’t change. I want to group 30 records at the detail level, not 30 orders and not 30 ship methods). I think the group on function is for records that share something in common, to avoid having to repeat values, such as the ship method and order number as I am already doing. Once I get to the detail level, my records don’t have anything in common under which to group on.
But thank you for the idea. Any help is appreciated.
I have worked a lot in Access and can be dangerous with VBA, so even vague suggestions are helpful. I will dig into the details of how to execute it.
I am thinking I should add a field to the query to do some kind of cumulative total that I can manipulate, but Access doesn’t do cumulative totals well, either. If I add something to group on at the detail level, some kind of if statement? Anyone know how to do cumulative totals (or running totals) of record count within a query?
Anyone have any further ideas? Any help is appreciated. Thanks!
