Results 1 to 14 of 14
  1. #1
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    50

    Print Invoices (Multiple Copies) AND Keep Together

    Good Morning!

    I'm having trouble - hope someone can help.

    When I print my invoices, multiple customers all at once and two (2) copies of each report for each customer, Access prints all of the first page then all of the second page. We then have to match them up.



    Ideally - Access would automatically print two of each.

    Two for customer A then two for Customer B INSTEAD of 1 for Customer A then 1 for Customer B.

    Hope my issue clear. HOPE someone can help.

    Thx!
    Jason

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,174
    Can you show us the code you use for the printing.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,256
    Can you post the code where you are printing the invoices? Or a copy of the database?
    You may get some ideas from the Similar Threads at bottom of the page.
    Geez Bob you're quick!

  4. #4
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    50
    Private Sub cmdPrint_Click()
    On Error GoTo Error_Handler


    If Me.BeginDate = "" Or IsNull(Me.BeginDate) Then
    MsgBox "Please Enter a Ship Date", vbOKOnly, "Incomplete Data"
    Me.BeginDate.SetFocus
    Exit Sub
    End If


    If Me.cboDriver = "" Or IsNull(Me.cboDriver) Then
    MsgBox "You must select a Driver...", vbOKOnly, "Incomplete Data"
    Me.cboDriver.SetFocus
    Exit Sub
    End If


    If DCount("*", "qryCheckForInvoicesToPrint") = 0 Then
    MsgBox "There are no invoices to print that have not already been printed or there are not confirmed invoices for this driver.", vbOKOnly + vbCritical + vbDefaultButton4, "Attention!"
    Exit Sub
    End If
    If DCount("*", "qryCheckForUnconfirmedOrders") > 0 Then
    MsgBox "There are unconfirmed invoices for this day/driver. Please confirm all invoices for this day/driver before printing.", vbOKOnly + vbCritical + vbDefaultButton4, "Attention!"
    Exit Sub
    End If

    If vbYes = MsgBox("Are you sure you want to send two copies of each confirmed but unprinted invoice directly to the printer?", vbYesNo + vbQuestion + vbDefaultButton2, "Question?") Then
    DoCmd.SetWarnings False


    DoCmd.OpenReport "rptSalesInvoicesByDriver", acViewPreview
    DoCmd.PrintOut , , , , 2


    DoCmd.OpenQuery "qryUpdateSalesInvoicesByDriverInvoicePrinted" , acViewNormal, acEdit
    If IsFormLoaded("frmReport_SalesInvoicesByDriver") Then
    DoCmd.Close acForm, "frmReport_SalesInvoicesByDriver"
    End If
    DoCmd.SetWarnings True
    End If


    DoCmd.Close acReport, "rptSalesInvoicesByDriver"
    DoCmd.Close




    Exit_Procedure:
    On Error Resume Next
    DoCmd.Hourglass False
    ' Me.cmdCancel.Enabled = True
    Exit Sub


    Error_Handler:
    MsgBox "An error has occurred in this application." & Err & ", " & Error & vbCrLf & vbCrLf & _
    "Please contact your technical support person and report the problem.", vbExclamation, "Error!"
    ErrorLog Me.NAME & "_PrintDriverInvoices", Err, Error
    ' Put the focus back in the database window
    DoCmd.SelectObject acTable, "ErrorLog", True
    Resume Exit_Procedure
    End Sub

  5. #5
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    50
    If I'm printing 20 invoices (2 copies of each), it prints the first 20 then the 2nd twenty and we have to match them up. Not a huge deal but I'm surprised not an easier way to do this.

    I've looked into doing the way I print Labels and Barcodes but looks like it would be a huge pain to figure out with so many tables and data fields in the Invoice Report.

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,174
    Try this:
    Code:
    Private Sub cmdPrint_Click()
    On Error GoTo Error_Handler
    Dim i as Integer
    
    If Me.BeginDate = "" Or IsNull(Me.BeginDate) Then
    MsgBox "Please Enter a Ship Date", vbOKOnly, "Incomplete Data"
    Me.BeginDate.SetFocus
    Exit Sub
    End If
    
    
    If Me.cboDriver = "" Or IsNull(Me.cboDriver) Then
    MsgBox "You must select a Driver...", vbOKOnly, "Incomplete Data"
    Me.cboDriver.SetFocus
    Exit Sub
    End If
    
    
    If DCount("*", "qryCheckForInvoicesToPrint") = 0 Then
    MsgBox "There are no invoices to print that have not already been printed or there are not confirmed invoices for this driver.", vbOKOnly + vbCritical + vbDefaultButton4, "Attention!"
    Exit Sub
    End If
    If DCount("*", "qryCheckForUnconfirmedOrders") > 0 Then
    MsgBox "There are unconfirmed invoices for this day/driver. Please confirm all invoices for this day/driver before printing.", vbOKOnly + vbCritical + vbDefaultButton4, "Attention!"
    Exit Sub
    End If
    
    If vbYes = MsgBox("Are you sure you want to send two copies of each confirmed but unprinted invoice directly to the printer?", vbYesNo + vbQuestion + vbDefaultButton2, "Question?") Then
    DoCmd.SetWarnings False
    
    Do Until i=2
    DoCmd.OpenReport "rptSalesInvoicesByDriver", acViewPreview
    DoCmd.PrintOut , , , , 1
    i =i + 1
    Next
    
    DoCmd.OpenQuery "qryUpdateSalesInvoicesByDriverInvoicePrinted" , acViewNormal, acEdit
    If IsFormLoaded("frmReport_SalesInvoicesByDriver") Then
    DoCmd.Close acForm, "frmReport_SalesInvoicesByDriver"
    End If
    DoCmd.SetWarnings True
    End If
    
    
    DoCmd.Close acReport, "rptSalesInvoicesByDriver"
    DoCmd.Close
    
    
    
    
    Exit_Procedure:
    On Error Resume Next
    DoCmd.Hourglass False
    ' Me.cmdCancel.Enabled = True
    Exit Sub
    
    
    Error_Handler:
    MsgBox "An error has occurred in this application." & Err & ", " & Error & vbCrLf & vbCrLf & _
    "Please contact your technical support person and report the problem.", vbExclamation, "Error!"
    ErrorLog Me.NAME & "_PrintDriverInvoices", Err, Error
    ' Put the focus back in the database window
    DoCmd.SelectObject acTable, "ErrorLog", True
    Resume Exit_Procedure
    End Sub
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    50
    I get a compile error "Next without For"

    Click image for larger version. 

Name:	error.jpg 
Views:	22 
Size:	289.3 KB 
ID:	45939

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,256

  9. #9
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    50
    I was praying... Switching Next with Loop eliminated the compile error but the still printed all 1st copies then all 2nd copies..

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,256
    Can you post a copy of the database with only a few records? We don't need real data to review the processing logic. You could change a few names to Porky Pig, Barb Dwyer, Owen Money, Justin Tyme etc.
    I'm quite sure it is a matter of adjusting some logic/looping, but easier to review existing process than create something from scratch.

    You may research this.

  11. #11
    Kaloyanides is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Arlington, MA
    Posts
    50
    Something I've struggled with for years... Can't figure it out...

    If I print 10 reports, 2 copies of each, it prints all of the first copies then all of the 2nd copies.

    By report I mean Invoices... I want to print 2 copies of each invoice.

    It prints all of the 1st copies, then all of the 2nd copies, INSTEAD of first copy then 2nd copy, first copy, then 2nd copy, first copy, then 2nd copy...

    It's a pain to match them up for each route every day.

    Posting my database would take a ton of time because it's so big having been developed over so many years... I'd have to strip it down and remove all of the data.

    If anyone can help me out with this, I would be HUGE! Thx so much in advance. I've posted the code I use to generate the reports above.

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    2,524
    You could indent your code plus posting a picture of whatever that is does not help.
    If you replaced the next with Loop, then I cannot see why that would not work?

    Have you even walked through your code to see what it does not what you think it does?

    Alternatively try
    Code:
    For i = 1 to 2
        Openreport
        PrintOut
    Next
    That is psuedo code inside the loop BTW, use your current commands.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,206
    To be clear you want your output to be in this order to print invoices

    inv1 page1 copy1
    inv1 page1 copy2
    inv1 page1 copy3
    inv2 page1 copy1
    inv2 page1 copy2
    inv2 page1 copy3

    If so, I would modify your report recordsource to include a counter table - in this case, looks like this

    tblCounter
    Counter
    1
    2
    3

    Then modify your report recordsource (if doing in the report, use the report open event) to include the table but not joined to anything e.g.

    me.recordsource="SELECT * FROM tblCounter, (" & me.recordsource & ")"

    you can even include a control on your report called say 'Copy' with a controlsource of Counter


    You can also use this to modify the number of copies required by filtering in the docmd.openreport where parameter which would be "Counter<=2" or openargs parameter or with criteria

    me.recordsource="SELECT * FROM tblCounter, (" & me.recordsource & ") WHERE Counter<=" & me.openargs

    or perhaps
    me.recordsource="SELECT * FROM tblCounter, (" & me.recordsource & ") WHERE Counter<=" & forms!invoiceprintform!Copies

    Can even reverse the order if it matters

    However better to just modify your recordsource and be done with it

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,897
    Not sure why the loop when you could specify the number of copies. Also, there is a collate parameter that you're not using. This is one that I'd like to dabble with seeing as how I've never had to worry about it, but ink is kind of pricey...

    Perhaps review this to see if it helps with that code line
    https://docs.microsoft.com/en-us/off...docmd.printout

    BTW - perhaps in the last year or so since this was originally posted you've already been asked to post properly indented code using code tags (see # button on posting toolbar) rather than use pics or just dumping in the text. If so and you've already adapted, please just ignore this part and sorry for repeating the request.

    Dang! Too slow again!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-14-2020, 02:09 PM
  2. Replies: 1
    Last Post: 01-07-2015, 12:04 PM
  3. print multiple copies
    By slimjen in forum Reports
    Replies: 4
    Last Post: 08-14-2014, 06:33 PM
  4. Replies: 3
    Last Post: 02-04-2014, 04:33 PM
  5. Can docmd.openreport print X copies on Y printer?
    By Coolpapabell in forum Reports
    Replies: 1
    Last Post: 09-02-2009, 08:35 AM

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