Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Upstate, New York, USA
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing 2 reports simultaneously (Access 2000/SR1a)

    Some background: I run two reports; one is a check, the other is backup information for the check (the two are linked on a field, "CHECK_KEY"). Depending on the day, there may be 250 checks plus backups to be run. Currently we have to combine the two by hand, which takes hours. This must be done because the checks print on check stock, and the backup prints on plain paper.

    I've been able to run the reports simultaneously from different printer trays (set in the report's page setup) using the following code behind a button on a form:

    <font color=red>Private Sub Command0_Click()
    stDocName = "Report1"
    DoCmd.OpenReport stDocName, acNormal
    stDocName = "Report2"
    DoCmd.OpenReport stDocName, acNormal

    End Sub</font color=red>

    This works when there is only one matching record in each report's underlying query. When there is more than 1 record, it runs all the checks first, then all the backups.

    What I need is to be able to iterate through the records, so I can print the first check, then the first backup, then the next check, the next backup, etc. I found the following sample code, but it's not working for me:

    <font color=red>Sub Iterate()
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from Table1")
    rs.MoveFirst

    Do Until rs.EOF
    DoCmd.OpenReport "Report1", acViewNormal, , "pkID = " & rs!pkID
    DoCmd.OpenReport "Report2", acViewNormal, , "pkID = " & rs!pkID
    If Not rs.EOF Then rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    End Sub</font color=red>

    Does anybody have suggestions? I'm desperate to get this working. Thanks in advance.

    Kyle

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing 2 reports simultaneously (Access 2000/SR1a)

    You state 'but it's not working for me' about the sample code in your post. In what way?
    - It causes an error message (if so, what does it say and where does it occur?)
    - It produces incorrect results (if so, what is wrong?)
    - It doesn't do anything at all.

    ACC2000: Using Visual Basic Code to Collate and Print Two Reports contains another example; this one prints one page from the first report, then one page from the other one, etc.

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    Upstate, New York, USA
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing 2 reports simultaneously (Access 2000/SR1a)

    Hans, thank you for your response. My apologies for not being complete in my first post (explaining the error) - usually I know better than that.

    The link that you provided does almost exactly what I'm looking for. However, it requires that the number of pages be provided prior to running the function. Is it possible for the function to determine the total number of pages at runtime? This would be ideal, because there will always only be 1 check page per record, BUT there could be multiple pages of backup for a single check (e.g., check number 12345 will print, then a 1 page backup will print subsequently; check 12346 will print, but its backup is 8 pages). I'd like to be able to do this without the user having to determine the number of pages in the reports prior to executing.

    I hope I've explained this properly. If not, please let me know and I'll try again. Thanks again for your prompt answer.

    Kyle

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing 2 reports simultaneously (Access 2000/SR1a)

    Problem is that you don't know the number of pages a report will run before you open it (in Normal or Preview mode), unless you have other means of determining this number. So DoCmd.Printout is probably not the way to go. The method you described in the first post in this thread seems better suited to your needs. Perhaps we can find a way to make it work the way you want if you describe what problems you had with it.

  5. #5
    Lounger
    Join Date
    Feb 2001
    Location
    Upstate, New York, USA
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing 2 reports simultaneously (Access 2000/SR1a)

    When I run the code shown in my first post by clicking the button on the form, I get an error that reads "Compile error: user-defined type not defined", with the first line ("Dim db As dao.Database") highlighted in the VB editor.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing 2 reports simultaneously (Access 2000/SR1a)

    That's easy to solve. Select Tools | References... (in the VB Editor), locate and check Microsoft DAO 3.6 Object Library, then click OK.

  7. #7
    Lounger
    Join Date
    Feb 2001
    Location
    Upstate, New York, USA
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing 2 reports simultaneously (Access 2000/SR1a)

    Hans, I can't thank you enough. That got things going, of course.

    Now for my next question, if you don't mind. When I run this procedure now, it's asking me to enter my linked fields as parameters. Here's the actual code:

    Private Sub Command0_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from ALYCE_REPORTS_CHECK_PRINTING_QUERY")
    rs.MoveFirst

    Do Until rs.EOF
    DoCmd.OpenReport "Report1", acViewNormal, , "CHECK_KEY = " & rs!CHECK_KEY
    DoCmd.OpenReport "EOB", acViewNormal, , "CHECK_KEY = " & rs!CHECK_KEY
    If Not rs.EOF Then rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    End Sub

    If the CHECK_KEY field's value is "K1H4", I get a prompt to enter a value for "K1H4". I'm pretty sure I'm just not referring to that field correctly. These 2 reports are based on 2 separate queries, ALYCE_REPORTS_CHECK_PRINTING_QUERY and ALYCE_REPORTS_EOB_PRINTING_QUERY. I tried entering the query name before each CHECK_KEY, but I think I'm using the wrong syntax. Can you help me with this as well?

    I can't tell you how much time this is going to save us; your advice is making my job a LOT easier!

    Kyle

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing 2 reports simultaneously (Access 2000/SR1a)

    From the example you give, CHECK_KEY is a text field. You must surround text values with quotes. An easy way to insert quotes in a string you're assembling is to use Chr(34).

    DoCmd.OpenReport "Report1", acViewNormal, , "CHECK_KEY = " & Chr(34) & rs!CHECK_KEY & Chr(34)
    DoCmd.OpenReport "EOB", acViewNormal, , "CHECK_KEY = " & Chr(34) & rs!CHECK_KEY & Chr(34)

  9. #9
    Lounger
    Join Date
    Feb 2001
    Location
    Upstate, New York, USA
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing 2 reports simultaneously (Access 2000/SR1a)

    Hans, I can't thank you enough. This is working *exactly* as we needed! Everyone here is thrilled that we won't have to collate these reports manually anymore.

    Thanks again for your time and advice.

    Kyle

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •