Question about MS Access Code involving Printer.PaperBin RRS feed

  • Question

  • Below is a copy of a post/question/problem that I have.  I wasn't sure where to post it or if any of the MSDN forums would work.  I took a look at the VBA forum and the first page anyway looked to be simple questions around Excel or something.  I wasn't sure if this question would work in that forum.  Any thoughts?
    - JJ
    ***** My Post ****************
    The following line of code works great for a handful of PCs at our agency, but fails when run on other PCs:

    'assign the PaperBin that is currently attached to the "report of interest"
    'to "prt", which is an Access.Printer defined above.
    prt.PaperBin = Reports(stReportNm).Printer.PaperBin

    There are two error messages that appear at this line of code, depending on the PC:
    1:  "Description 5: Invalid procedure call or argument."
    2:  "An invalid character was found in text content."

    This is in Access 2003.  On Windows XP, SP3.

    In some cases, staff need to print to a specific printer or define other aspects of the report before printing.  For those reports, I bring up a custom print dialog box.  The line of code listed above is a line of code in the customer dialog box.

    The custom dialog box lets the user define the following: 1) the printer, 2) whether or not to duplex, 3) whether to print all pages or a range of pages, 4) number of copies.

    The custom dialog box does NOT bother the user with choices like whether to print say landscape vs portrait.  Instead, the report is saved with the correct setting for landscape vs portrait.  When the user clicks the Print button in the custom screen, the code does the following:

    1) creates a Access.Printer object based on the drop down list, txtPrinter, found in the form.  The user has picked a printer in the drop down list.
    Set prt = Application.Printers(CLng(Me.txtPrinter.Column(0)))

    2) assigns values from the form to the printer object.  Example:
    prt.Copies = Me.txtCopies 'assign the number of copies to prt

    3) opens the report in preview mode
    DoCmd.OpenReport stReportNm, acViewPreview

    4) Copies over some of the properties, like paper orientation and paper bin, from the report's printer object to the "prt" variable.  Examples:
    prt.Orientation = Reports(stReportNm).Printer.Orientation
    prt.PaperBin = Reports(stReportNm).Printer.PaperBin

    The subroutine fails on some PCs at the paper bin line. 

    The paper bin line is important because some reports print to envelopes or letterhead.  Without specifying the bin, the report goes to the wrong bin. 

    For the reports in question: I know which printer the staff will pick.  They pick the same printer that I pick as my computer's default printer when I'm working with the report.  Then I open the report in design view and set the paper bin among other settings.  So, when the user picks the same printer, the paper bin that is assigned to the report should be OK.  Why would it work on my PC and some others, but fail on most of them?

    When I print from my own PC, the code runs just fine.  I added the following line:
    '  Find out what number is currently assigned to the PaperBin of the report in question.
    Debug.Print "Paper bin: " & Reports(stReportNm).Printer.PaperBin
    The answer was:
    Paper bin: 1270
    I've read on some web pages that there is a problem setting Printer.PaperBin to a number greater than 255.  If you think that is my problem here, I have these questions:
    a) Why is the code working on some PCs and not others?
    b) Why would there be a problem at all if the user is picking the same printer that I had picked when I set up the report and all the code is doing is re-assigning the same bin to the same printer object?
    c) Is there a patch or Windows setting or something to fix this problem?  So that I can maintain the same functionality?

    Or since the code works sometimes, do you think the problem has nothing to do with the number of the paper bin?  What else could be going on?

    THANK YOU for any help you can provide!!!
    Wednesday, September 23, 2009 9:01 PM


All replies