Batch Printing a report for multiple records

So I have a database used to store orders. For each order it generates a picking list and a delivery report. If I am on the order record in the form then clicking my print button will run both macros and print the reports with their addresses, and corresponding totals, and then set the printed field to today's date.

However if I import the orders in a batch, then I would have to view each order in the form and print separately. I want to make a piece of code, or macro that will print the picking list report and delivery report, and run a stock update query and update the printed field, for every order record in which the printed field is null.

I tried setting the criteria for the report to Is Null (printed) but it just combined all of the order details for the separate orders onto one report, only giving it one address and total. I'm fairly sure this is to do with how the detail in the report is set up, but I can't put the address in there otherwise it would be repeated for every part they have ordered.

Maybe there is something in coding to do with an array or something? I'd be grateful for any assistance.

Who is Participating?
ChrisGKnightConnect With a Mentor Author Commented:
Right. My batch list would be a query though, so I don't know how I would loop it through that.

But I think I've got it to work by using the grouping in the report, setting that to the orderID, and putting addresses and totals in the group header and footer, and forcing a new page after the footer. So it only puts one order per page, but runs for every orderID which has printed as null.

Haven't got to the queries yet, but I'm hoping that will be easier.

Thanks for the response though :)
clarkscottConnect With a Mentor Commented:
In code:
1.  Create a public variable to contain the Primary Key for the reports
Public glbRecordID as long

2.  Create a function to 'return' this value.
Function get_glbRecordID() as long
get_glbRecordID = glbRecordID
End Function

3.  Create a query of the report data.  In the criteria - add the primary key value and put "= get_glbRecordID()" for criteria.

4.  Open your report in design mode and set the recordsource to this new query


1.  Open the 'batch' list.
2.  Loop thru all records, one at a time.
    3.  Identify the primary key for the individual report
    4.  Set the public variable to the value of this single primary key (glbRecordID = YourPK)
    5.  Open the report for printout
         Note: By setting the public variable to a value, the report's query will automatically use the    glbRecordID for criteria.  You don't have to do anything else.
    6.  move to next record
Loop until finished.

Scott C
You can open a query just like any other table.
I don't know your 'code' experience, though.  My suggestion requires VBA coding.
Scott C
ChrisGKnightAuthor Commented:
Yes, my VBA knowledge is quite limited. If I had the time I would go through to try your method. But at the moment the alternative method I found seems to do what I need it to.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.