[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Batch Printing a report for multiple records

Posted on 2009-12-16
Medium Priority
Last Modified: 2013-11-28
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.

Question by:ChrisGKnight
  • 2
  • 2
LVL 20

Assisted Solution

clarkscott earned 1000 total points
ID: 26062273
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

Accepted Solution

ChrisGKnight earned 0 total points
ID: 26062317
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 :)
LVL 20

Expert Comment

ID: 26062344
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

Author Comment

ID: 26062373
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.


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question