Batch Printing a report for multiple records

Posted on 2009-12-16
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
    LVL 20

    Assisted Solution

    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

    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

    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

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now