Programatically selecting Paper bin

J

Jael

I have re-posted here after not getting a reply under Reports.
I’m using Access 2003 with Vista Ultimate

I use a form to select one of several reports with data from one of several
mailing lists. I want the selected report to print using the label sheets in
the manual bin of the default printer. The current program works but uses the
wrong bin.

referring to a link supplied in a previous post
http://support.microsoft.com/default.aspx?scid=kb;en-us;279506
I copied the code below. Me.txtstrCrit is a simple SQL string that filters
the mailing list. The code will not compile as the "Set Prt = ..." line
errors with a "Argument not Optional".

When I enter the code in VBA “Reports (…†I’m prompted for “start
date, end date, dbs, tagâ€, not a name. The referenced link uses the format
I used but it is for access 2002 - is there a change in this area for 2003?.

Any suggestions would be appreciated.

Thanks,
Jael

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click
Dim stDocName As String
Dim Prt As Access.Printer
stDocName = "rptMailMerge Labels"
DoCmd.OpenReport stDocName, acViewDesign, , , , Me.txtstrCrit
Set Prt = Reports(stDocName).Printer 'This will not compile
Prt.PaperBin = acPRBNManual
DoCmd.PrintOut
DoCmd.Close acReport, stDocName, acSaveNo
Exit_cmdPrint_Click:
Exit Sub
Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click
End Sub
 
D

Douglas J. Steele

Perhaps you can post the actual code you're using, in case you didn't
translate from the sample to your case correctly.
 
J

Jael

Douglas,
Thanks for the response - The following is the exact code I'm trying to use.
It is for a Print Command button in a form called frmSelectlabelType

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String
Dim Prt As Access.Printer

stDocName = "rptMailMerge Labels"

DoCmd.OpenReport stDocName, acViewPreview, , , , Me.txtstrCrit
Set Prt = Reports(stDocName).Printer 'I GET A COMPILER ERROR HERE
Prt.PaperBin = acPRBNManual
DoCmd.PrintOut
DoCmd.Close acReport, stDocName, acSaveNo

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub
 
D

Douglas J. Steele

I haven't had a chance to test on a Vista machine, but that code works fine
in Access 2003 under XP.

It might be worth checking that your references are okay. While in the VB
Editor, select Tools | References from the menu, and make sure none of the
selected references (they're at the top of the list) have MISSING: in front
of them.
 
J

Jael

Douglas,
Well I tried the program in Access 2003 on my XT machine & get the same
results. So I suspect the correct reference isn’t there, but I have no idea
what reference from the list it would be. (VBA ver. 6.3, XT SP3)
Following Tools | References - My current references are:

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library

These are all check boxes and there is no "Missing" note or text.
Are there any standard references I'm not using and should be?

The list is a couple hundred long.
Jeff
 
D

Douglas J. Steele

No additional references are required. Afraid I don't know what to suggest.
 
J

Jael

Appreciate your effort. If I figure it out, I'll post back to this thread.

I'm trying:

Application.Printer.PaperBin = acPRBNManual

But not too sure where to insert it.

Before doCmd.Print or in detail.Print or ?

Thanks,
Jeff
 
J

Jael

AHA!!!
When all else fails, try the obivous...Occams' Razor.

After your response, the only way I could figure was a prior definition of
"Reports"

Sure enough there was a function named "Reports (Startdate,enddate,dbs,tag)"
for monthly reports (not used in this application). Once removed/renamed,
the compile is GOOD!

Thank you for validating what I had so my focus was free to look elsewhere.

Jeff -A California Desert Rat
 
J

Jael

Thanks for the link - Quite a few of them. Yes I was aware of improper words
but this one slipped thru a couple years ago as an addition to a common
global module. Oh well even at 70 yo I can still learn - and I enjoy
reading posts by you, Allen Browne, and the others.

Appreciate it,
Jeff
 
Top