Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Understanding vba control of printer bins

Posted on 2006-06-28
10
Medium Priority
?
802 Views
Last Modified: 2009-11-06
I'm trying to get vba code that I can use to specify which printer bin is used for printing (to get some things to print on headed paper).  When I'm printing a report I've been using the code below (called from a button using Call PrintPages(Me.cboReportList, Me.cboFirstPage)).  I've tested all the possible Access constants on the relevant printer and they all printed out via the default tray (i.e. don't seem to be understood), except the Cassette constant which worked.  On another printer I got the middle tray working using the Upper tray constant!  Is there any way to use these constants in a less hit and miss way?

Then I found code of how to find what bins (names & numbers) are available on a certain machine.  This returned a list of about 25 bins for my main printer, with numbers from 15 to 1257.  I don't undestand how to use these.  Are they perhaps for use when controlling the printing of a Word document rather than an Access report?

Private Sub PrintPages(strReport As String, _
 PaperBin As AcPrintPaperBin)
   
    Dim rpt As Report
 
    On Error GoTo HandleErrors
 
    DoCmd.OpenReport strReport, acViewPreview, WindowMode:=acIcon
    Set rpt = Reports(strReport)
    rpt.Printer.PaperBin = PaperBin
   
    ' Unfortunately, you have to select the
    ' report in order to print it. Who wrote
    ' the PrintOut method this way, anyway?
    DoCmd.SelectObject acReport, strReport
    DoCmd.PrintOut acPages, 1, 1
   
ExitHere:
    DoCmd.Close acReport, strReport, acSaveNo
    Exit Sub

HandleErrors:
    MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
    Resume ExitHere
End Sub

0
Comment
Question by:looper8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 1

Author Comment

by:looper8
ID: 17002239
OK, so since I posted I've discovered that the list of about 25 numbers I got can be use in the following way.  When printing out a Word document from Access you can do something like this:

With m_objWord.ActiveDocument
        .Save
    ' First page goes to letterhead, others to plain
        .PageSetup.FirstPageTray = 263
        .PageSetup.OtherPagesTray = 262
        .PrintOut
    End With

So if it's a Word doc looks like I'm OK ... but I'm principally needing to print Access reports!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17002320
what if u output your report to Word then do your word auto like u have demonstrated?
0
 
LVL 1

Author Comment

by:looper8
ID: 17012831
Thanks rockiroads.  I ought to give that a try.  I initially dismissed it because I assumed that it would change the report layout too much ... but my assumptions aren't always right to say the least!

Got a couple of days off now (hallelujah) so will get back to you after the weekend.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 65

Expert Comment

by:rockiroads
ID: 17013388
Nice, have a good weekend then

Mine is just an alternative option, there may be better ways, I don't know
0
 
LVL 1

Author Comment

by:looper8
ID: 17035773
For once my assumption was correct - Word changes the report formatting quite considerably.  So I'm stuck with trying to output using the Access constants (acPRBNAuto etc etc ... do a find on 'paperbin' in Object Browser).  Also a good relevant chapter on printing in the Access Cookbook.

Seems to vary from printer to printer as to how these constants work.  Bit hit and miss as to which constant is understood in which way by which printer - in a good number of cases the printer doesn't understand at all and just uses the default paper tray.  Anyhow I think I can do enough on one of our printers to keep the users happy.
0
 
LVL 1

Author Comment

by:looper8
ID: 17043882
I've gone with the following.  I've changed the printer for each relevant report to the specific one I can manage to control with acPRBNLower.  And  it'll be fine unless that printer goes down!  I've given the users a combo box to change their Access default printer to any other installed printer, but I think that will be overridden by the specific printer specified in the reports.  I'll see how it goes!

'Open and print the report
   
    strLinkCriteria = " a.AllegationID = " & Me.txtAllegationID        
    DoCmd.OpenReport strReport, acViewPreview, , strLinkCriteria, WindowMode:=acIcon    
    Set rpt = Reports(strReport)
   
    'Even though this says Lower for the paper bin, it prints out from the upper bin of the HPLJ212 in SPU/CAT!!
    rpt.Printer.PaperBin = acPRBNLower
   
    DoCmd.SelectObject acReport, strReport
    DoCmd.PrintOut acPages, 1, 1
   
End If
   
ExcludeLetter_Exit:
    DoCmd.Close acReport, strReport, acSaveNo
Exit Sub
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17045917
Sorry I couldnt of been more help

I just had another look

I found this though, it does refer to Word VBA, but u may be able to make use of it
http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=101

and this  http://support.microsoft.com/?kbid=279506
which is basically what u have done
0
 
LVL 1

Author Comment

by:looper8
ID: 17071138
Thanks rockiroads, I'd actually already seen those docs.  Of course one thing we didn't mention was to set each report's properties to print to a specific printer.  But I think that gives the same options as we've already had.  Something else I'm going to try is to install a second copy of a particular printer I want to control onto the server, and then play around with the settings (thus not interfering with day-to-day print jobs).  Maybe it'll be useful ...
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 17076277
Closed, 500 points refunded.
Netminder
Site Admin
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question