Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Nov 2013
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question Double sided printing

    Hello Loungers

    I have been trying for some time to solve a report printing problem I have in MS Access 2010 and I am hoping someone here can help.

    I have a report that shows detailed information about user access to an accounting system. Each user report can be one or multiple pages. Each user report should print on a separate sheet or multiple sheets of paper. The User header has the Force New Page property set to Before Section.

    If I print the report double-sided, any time a user report has an odd number of pages, the next user report starts on the back side of the sheet.

    Is there a way to force each user report to be an even number of pages so that the duplexing option works properly?

    The database is an old .mdb file but I am willing to update it if it will fix the problem.

    Thank-you

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,496
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Robjd,

    See if this MS article helps.
    Click on the Show All link then check out the section on "Force a page break if a condition is met.: HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    Silver Lounger mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    2,187
    Thanks
    210
    Thanked 213 Times in 205 Posts
    I wonder, if you set your printer preferences to not print duplex (i.e. on the back of the page), would the printer preferences override the Access settings.

    When you tell it to print, you can set your printer preferences in the pop-up print window.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,496
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Robjd,

    Another way to solve this problem is to call your Report from a macro that loops through your users list and passes the user as a filter parameter to the report thus you will print many reports each only containing one user. I use this procedure to create annual dues bills for our HOA.

    Here's a portion of the code that contains the loop and call to the report:
    Code:
     
    '                         +-------------------------+             +----------+
    '-------------------------|      EmailBills()       |-------------| 01/11/12 |
    '                         +-------------------------+             +----------+
    'Requires : PDFCreator {Open Source PDF Printer Driver}
    '           Sleep      {Windows API Function Declaration}
    'Called By:
    'Calls: ClearPDFDirectory()
    '       SetDateForBills()
    '       [Utilities] SwitchPrinters()
    '       [Utilities] zGetDBPath()
    
    Sub EmailBills()
    
       Dim dbName     As Database
       Dim rst        As Recordset
       Dim lRecNo     As Long
       Dim lBillCnt   As Long
       Dim zWhere     As String
       Dim zMsgBody   As String
    #If LateBinding = 0 Then    'Early Binding
       Dim appOL      As Outlook.Application
       Dim miMail     As Outlook.mailitem
    #Else
       Dim appOL      As Object
       Dim miMail     As Object
    #End If
    
       Dim oMyAttach  As Object
       Dim zAttFN     As String
       Dim zBillPath  As String
       
       If Not SetDateForBills() Then
         Exit Sub
       End If
       
       MsgBox "Please Note:" & vbCrLf & vbCrLf & _
              "If Microsoft Outlook is Closed the created Emails " & vbCrLf & _
              "will be sent to the INBOX folder." & vbCrLf & vbCrLf & _
              "If Microsoft Outlook is OPEN {recommended} the created Emails " _
              & vbCrLf & "will be sent to the DRAFTS folder." & vbCrLf & vbCrLf & _
              "When OUTLOOK is properly set press OK", _
              vbOKOnly + vbInformation, _
              "IMPORTANT INFORMATION:"
              
       zBillPath = zGetDBPath() & "EmailBills\"
              
       ClearPDFDirectory
       strDfltPrt = Application.Printer.DeviceName
       SwitchPrinters "PDFCreator"
       
       Set appOL = CreateObject("Outlook.Application")
       Set dbName = CurrentDb()
       Set rst = dbName.OpenRecordset("Owners", dbOpenDynaset)
       rst.MoveFirst
       
       lBillCnt = 0
       zMsgBody = "Please find your WPOA annual dues statement attached." & _
                  vbCrLf & vbCrLf & "WOPA Board of Directors" & vbCrLf & _
                  vbCrLf & "Attachment: "
       Do
         If (rst![EMailDocs] And rst![EMail] <> "") Then
         
           zWhere = "[OwnerID] = " & Str(rst![OwnerID])
       
    'Note: If acNormal is selected the report is send automatically to the
    '      Default printer!
    '      If acPreview is selected the report is sent to the screen.
    
           DoCmd.OpenReport "rptAnnualBilling", acNormal, , zWhere
       
    '******* Rename file with OwnerID
    
    On Error GoTo WaitForPDFCreator
    Try_Again:
    
           Do While Dir(zBillPath & "rptAnnualBilling.pdf") = vbNullString
             Sleep 1250           '** wait 1.25 secs before trying again **
           Loop
           
           Name zBillPath & "rptAnnualBilling.pdf" As _
                zBillPath & "Bill" & Format(rst![OwnerID]) & ".pdf"
    On Error GoTo 0
    '******* Begin Send Email
    
    #If LateBinding = 0 Then
           Set miMail = appOL.CreateItem(olMailItem)  '*** olMailItem = 0 ***
    #Else
           Set miMail = appOL.CreateItem(0)
    #End If
    
           With miMail
               .To = rst![EMail]
               .Subject = "WPOA Annual Dues Statement: " & rst![OwnerLName]
               .Body = zMsgBody & "Bill" & Trim(Str(rst![OwnerID])) & _
                       " Owner: " & rst![OwnerLName]
               .ReadReceiptRequested = True
               zAttFN = zBillPath & "Bill" & _
                        Trim(Str(rst![OwnerID])) & ".pdf"
               Set oMyAttach = miMail.Attachments.Add(zAttFN)
               .Save
           End With   'miMail
    
           Set miMail = Nothing
           lBillCnt = lBillCnt + 1  '*** Count Emails Created ***
    
    '******* End Send Email
    
         End If
         
         rst.MoveNext        '*** Move to Next Record ***
       
       Loop Until rst.EOF
       
       MsgBox Format(lBillCnt, "#,###") & " Email Bills Created." & _
              vbCrLf & vbCrLf & _
              "Maximize Outlook and Press F8 and select the" & _
              "SendAllDrafts macro then click Run." & _
              vbCrLf & vbCrLf & _
              "If Outlook wasn't open when you created the Email" & _
              vbCrLf & "Bills you will have to move them to the" & _
              vbCrLf & "Drafts folder from the Inbox BEFORE you" & _
              vbCrLf & "run the macro!", vbOKOnly + vbInformation, _
              "Next Step:"
       GoTo GetOut
    
    WaitForPDFCreator:
       Select Case Err.Number
             Case 75
                 Sleep 0.75  '*** Wait another 3/4 second. ***
                 Resume Try_Again
             Case Else
                 MsgBox "Module:" & vbTab & "BillingsCode" & vbCrLf & _
                        "Routine:" & vbTab & "EmailMailBills" & vbCrLf & _
                        "Error: " & Err.Number & " " & _
                        Err.Description, vbCritical + vbOKOnly, _
                        "Unexpected Error:"
                 Resume GetOut
       End Select
       
    GetOut:
       Set rst = Nothing     '*** Close RecordSet ***
       Set oMyAttach = Nothing
       Set miMail = Nothing
       Set appOL = Nothing
       
       SwitchPrinters strDfltPrt
       
    End Sub                   '*** EmailBills() ***
    The above obviously contains stuff you don't need but pay attention to the establishment of the RecordSet (rst) the Do...Loop, and the DoCmd.OpenReport. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    Silver Lounger mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    2,187
    Thanks
    210
    Thanked 213 Times in 205 Posts
    RG, where did you learn Visual Basic for Word/Excel/etc? I have done some programming in my time, but it was a very long time ago, and it was in C and Pascal and never as a macro language.
    Last edited by mrjimphelps; 2014-02-12 at 13:55.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,496
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Jim,

    Almost all of my programming skills have been self taught. I did have formal training in Fortran & PL/1 (1970's), Advanced C (1980's). Everything else is done through reading books and a lot of trial and error...lots of error! I actually taught a course for programmers transferring from other languages to C. It is my OPINION that if you learn the fundamentals of programming (loops, tests, functions, etc) moving from language to language is merely a translation process (learning how the new language does those things). Needless to say moving to object oriented languages (VBA & Powershell) has been traumatic at times due to the paradigm shift but it a scalable mountain especially now that we have google, I google a lot!

    Of course I've gotten a lot of help in my quests from the Lounge!

    I can recommend some books if you are interested. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #7
    Silver Lounger mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    2,187
    Thanks
    210
    Thanked 213 Times in 205 Posts
    I was hired as a programmer once, to do C programming. The reason they offered me a job was because I had a background in Pascal programming.

    It's been a very long time (almost 20 years) since I have done anything more than batch file programming, but back then, I feel I had a good grasp of programming fundamentals.

    If you can recommend a good, simple book, that would be great.

    Maude had begun to post some help along these lines, but I believe it's been a while since she has posted.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,496
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Jim,

    One thing to remember about VBA..it is really composed of 2 things:
    1. VBA Control Language which is the same across office products.
    2. The Object Model for the Office program you are using. These are specific to the Office application.

    Because of this it is best if I recommend a book for the Office product you wish to use.
    For Access: Microsoft Access VBA Programming Second Edition for the Absolute Beginner, by Michael Vine ISBN:1-59200-723-6.
    For Excel: Special Edition Using Excel Visual Basic for Applications Second Edition, by Jeff Webb ISBN:0-7897-0269-X
    Please note that I've had these for a while so you will probably be looking at newer versions. Try to get one based on the version of the Office product you are using. HTH
    Last edited by RetiredGeek; 2014-02-12 at 16:35.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #9
    New Lounger
    Join Date
    Nov 2013
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks RG

    The MS article could be what I need but I will have to do a bunch more work. I am not a programmer, and my Access skills more or less stop at building expressions and setting object properties so I will need to work on this. I guess I was dreaming that there would be an easy property setting to do this.

    Based on my reading of the document here is what I think I need to do:
    At the end of the User footer I need to insert a hidden page break that only becomes visible/active if the page count is currently odd.
    Left to figure out: how to get the page count and determine whether it is odd or not.

    Any thoughts?
    Last edited by robjd; 2014-02-12 at 16:46. Reason: edited for clarity

  10. #10
    Silver Lounger mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    2,187
    Thanks
    210
    Thanked 213 Times in 205 Posts
    Quote Originally Posted by RetiredGeek View Post
    Jim,

    One thing to remember about VBA..it is really composed of 2 things:
    1. VBA Control Language which is the same across office products.
    2. The Object Model for the Office program you are using. These are specific to the Office application.

    Because of this it is best if I recommend a book for the Office product you wish to use.
    For Access: Microsoft Access VBA Programming Second Edition for the Absolute Beginner, by Michael Vine ISBN:1-59200-723-6.
    For Excel: Special Edition Using Excel Visual Basic for Applications Second Edition, by Jeff Webb ISBN:0-7897-0269-X
    Please note that I've had these for a while so you will probably be looking at newer versions. Try to get one based on the version of the Office product you are using. HTH
    I'm sure that VBA for Access is a more capable language than Visual Basic for Excel, because programming is a fundamental component of a database system (the kind of thing you use Access to design); whereas with Excel, programming seems more like a way mainly to enhance the basic product. In other words, programming is much more necessary with Access than with Excel.

    I think I'll start with the Excel book, since I'm already pretty well versed with Excel.

    Thanks for the info.

    By the way, here's something I'm familiar with: a great place to buy books is half.com. It's part of Ebay; and regular folks list their books there for sale. If you need textbooks for college, you usually can't beat their prices.
    Last edited by mrjimphelps; 2014-02-12 at 18:34.

  11. #11
    New Lounger
    Join Date
    Nov 2013
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by robjd View Post
    Thanks RG

    The MS article could be what I need but I will have to do a bunch more work. I am not a programmer, and my Access skills more or less stop at building expressions and setting object properties so I will need to work on this. I guess I was dreaming that there would be an easy property setting to do this.

    Based on my reading of the document here is what I think I need to do:
    At the end of the User footer I need to insert a hidden page break that only becomes visible/active if the page count is currently odd.
    Left to figure out: how to get the page count and determine whether it is odd or not.

    Any thoughts?
    Can anyone tell me if my summary is correct? I am back at this project haven't figured out how to do this yet.

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,519
    Thanks
    3
    Thanked 50 Times in 50 Posts
    Actually, I think the second suggestion RG made is a better one - it turns out that getting the current page number for a page being printed is quite convoluted when using Group By section breaks to force the new page. The basic concept is to open a recordset that contains all of the current users, and loop through the recordset printing the report using a filter of the user name to limit it to one user. In addition, it's a handy tool that can be used with lots of different reports once you master it. Also, the page numbering is always started at 1, and you can specify 1 of N in the page numbering, so you don't end up with pages 321 of 673 through 324 or 673 for a specific user.

    You will need to eliminate much of the code in RG's example related to the emailing function, but the portion that loops through the recordset should do the trick.
    Wendell

  13. The Following User Says Thank You to WendellB For This Useful Post:

    robjd (2014-03-26)

  14. #13
    New Lounger
    Join Date
    Nov 2013
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    Actually, I think the second suggestion RG made is a better one - it turns out that getting the current page number for a page being printed is quite convoluted when using Group By section breaks to force the new page. The basic concept is to open a recordset that contains all of the current users, and loop through the recordset printing the report using a filter of the user name to limit it to one user. In addition, it's a handy tool that can be used with lots of different reports once you master it. Also, the page numbering is always started at 1, and you can specify 1 of N in the page numbering, so you don't end up with pages 321 of 673 through 324 or 673 for a specific user.

    You will need to eliminate much of the code in RG's example related to the emailing function, but the portion that loops through the recordset should do the trick.
    Thank you for the reply WendellB. I don't think I will try to implement this right now. It is a bit over my head and I don't have time to work it all out. If I understand the solution correctly I would have to modify the report so that it just runs for one user id and create a query that generates a list of users. Then the VB would be run to take the user list and run the report for each name on the list. Way more than I can tackle right now.

    My attempts to put in a conditional page break aren't working due to problems with using the page number in a formula (I think it should be something simple like "If Page Mod 2 = 1 then") so for now I will continue printing this report one sided. If someone can help me get the page number it would be much appreciated.

  15. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,496
    Thanks
    212
    Thanked 852 Times in 784 Posts
    Rob,

    Ok, I've stripped out all the stuff you shouldn't need and commented where you need to make changes. HTH
    Code:
     
    
    Sub RunRptByUser()
    
       Dim dbName     As Database
       Dim rst        As Recordset
       Dim lRecNo     As Long
       Dim zWhere     As String
       
       Set dbName = CurrentDb()
    '*** Change Owners to the name of your database table
    '*** Containing the User Names
       Set rst = dbName.OpenRecordset("Owners", dbOpenDynaset)
       rst.MoveFirst
       
        Do
    '*** Change OwnerID the the field name of your USERID in both places below     
           zWhere = "[OwnerID] = " & Str(rst![OwnerID])
       
    'Note: If acNormal is selected the report is send automatically to the
    '      Default printer!
    '      If acPreview is selected the report is sent to the screen.
    
    '*** Change rptAnnualBilling to the name of your report ***
           DoCmd.OpenReport "rptAnnualBilling", acNormal, , zWhere
         
         rst.MoveNext        '*** Move to Next Record ***
       
       Loop Until rst.EOF
       
       Set rst = Nothing     '*** Close RecordSet ***
       
    End Sub                   '*** RunRptByUser() ***
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  16. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    robjd (2014-03-26),WendellB (2014-03-27)

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
  •