locked
Opening Multiple Report tabs of the same Report using different filters RRS feed

  • Question

  • Hi All,

    Im another newbie to Access and need your help please.

    Background: Im running MS Access 2007 and i have created my database and report template. I currently can generate the desired report for a given filter.

    Objective: I wrote a VB sub to read in a list of names that will serve as a filter input to my report. The sub should loop through these list of names and pass them to my report to generate that filter specific report. At the end of this sub i want to have multiple instances of the report per filter input. In other words...I need to generate separate reports for each filter (John Smith, Robert White...etc). I eventually want to export all the open reports to a PDF.(that’s a future step)

    Note: Currently my Report's filter gets its data from a saved query that expects a filter... ([Single Stat View Query]![Assigned To]). I did the following code:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim temp As String
    
    Set db = CurrentDb()
    
    Set rs = db.OpenRecordset("Count Name Query", dbOpenDynaset)
    
    Do While Not rs.EOF
    temp = rs![Assigned To]
    DoCmd.OpenReport "Personal Reports", acViewReport, , "[Assigned To]=" & rs![Assigned To]
    rs.MoveNext
    Loop
    
    Set rs = Nothing
    Set db = Nothing
    I get a syntax error on this statement. I know that i need to use NEW to create multiple instances of the report but im failing to pass the filters in the first loop iteration!!! can anybody advise?

     

    Please help!!!!!

    Friday, November 18, 2011 4:52 PM

Answers

  • I tested below code, that does the trick opening the Report and then exporting to PDF, closing the Report and then go to the next record.

     

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim MyPath As String
    Dim temp As String
    
    
    MyPath = "C:\YourPathName\"
    
    Set db = CurrentDb()
    
    Set rs = db.OpenRecordset("Count Name Query", dbOpenDynaset)
    
    Do While Not rs.EOF
    
    temp = rs("Assigned To")
    MyFileName = rs("Assigned To") & ".PDF"
    
    DoCmd.OpenReport "Personal Reports", acViewReport, , "[Assigned To]='" & temp & "'"
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFileName
    DoCmd.Close acReport, "Personal Reports"
    
    rs.MoveNext
    Loop
    
    Set rs = Nothing
    Set db = Nothing
    
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishani Thursday, November 24, 2011 3:37 PM
    • Marked as answer by danishani Wednesday, January 11, 2012 8:32 PM
    Wednesday, November 23, 2011 6:04 AM