Results 1 to 2 of 2
  1. #1
    beckysright is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2012

    Loop Query and Print based on multiple criteria

    I have a table "ExpenseReports" that has data imported from Excel for multiple people, each with different customers.

    I would like to create and print a separate query for each Employee with a separate page for each customer. So each of the following would be a separate sheet of paper: Page 1 - Employee1 & Customer 1; Page 2 - Employee1 & Customer2; etc.. In a dream world, I would like to then run an update query to mark is as printed so it doesn't show up when I run the next employee.

    I am not all that great at VBA but I did attempt with a module looping through and then filtering based on that but it didn't work. Current Fields:
    WeekDate, Customer, ExpType, CorpBooked, CurrencyMileage, Memo, WorkDays, Total, ResourceName, ImportDate, ExpenseID, QueryPrint

    Here is the Module I attempted to make:
    Public Sub OpenRecordset()

    Dim i As Integer

    Dim db As Database
    Dim rs As Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryExpenseReportsforPrint")

    For i = 0 To rs.RecordCount - 1
    Debug.Print rs.Fields("Customer")
    Next i

    Set rs = Nothing

    End Sub

    Any help would be greatly appreciated!!!!
    Thank you!

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Ottawa, ON (area)
    You should be using MS Access reports. A report can group and summarize the data almost any way you want, and you can set the page headers, group headers etc. as you want them to be. You can also set the conditions when a new page is required.

    In your case, your report groupings will be on Employee and then Customer (two levels), with a new page every time the customer ID changes. (I assume that Employee 1 will have multiple records for Customer 1, and multiple records for customer 2, etc.).

    You would (usually) run the report using VBA associated with a form command button, e.g.

    DoCmd.OpenReport "reportname", acPreview, , "filter expression"

    where reportname is the name of the report you want to run and filter expression is the criteria used to limit the records included in the report. (It is a "where" clause without the word WHERE)

    There is no need to have separate queries for each employee; the filter expression does it for you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-21-2017, 03:07 PM
  2. Replies: 8
    Last Post: 01-24-2017, 02:45 PM
  3. Replies: 9
    Last Post: 05-13-2014, 08:22 AM
  4. Replies: 5
    Last Post: 11-15-2012, 03:33 PM
  5. Replies: 0
    Last Post: 04-08-2010, 12:22 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums