Changing printer config in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maryj - 27 Oct 2006 13:00 GMT

Client has a macro to print a report. Is there a way to add some code to turn
off duplex printing? I'm not very fluent in VBA.

Thank you!

Signature

maryj

Reply to this Message

Tom Wickerath - 28 Oct 2006 01:56 GMT

If your client (and you) are using Access 2002 or 2003, I believe you might
find something in this article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/o
dc_acc10_printers.asp

Also worth checking out is this article:

Printer Selection Utility
http://allenbrowne.com/AppPrintMgt.html

Good luck,

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> Client has a macro to print a report. Is there a way to add some code to turn
> off duplex printing? I'm not very fluent in VBA.
>
> Thank you!

Reply to this Message

maryj - 30 Oct 2006 15:40 GMT

Thanks Tom.

I see in the 1st site there is a section dealing with duplex. It mentions a
Printer Property: Returns or sets an AcPrintDuplex constant indicating how
the specified printer handles the orientation of duplex (two-sided) printing.
An AcPrintDuplex constant can be one of the following:

acPRDPSimplex Single-sided printing with the current orientation setting.

Please help me understand how to put that into some code. I tried
Application.Printer.AcPrintDuplex = AcPRDPSimplex but get a compile error:
method or data member not found.

Please be patient with me - I am just not very knowledgable about VBA but
trying to learn more :)

Thank you!!!

Signature

maryj

> If your client (and you) are using Access 2002 or 2003, I believe you might
> find something in this article:
[quoted text clipped - 19 lines]
> >
> > Thank you!

Reply to this Message

Tom Wickerath - 30 Oct 2006 19:20 GMT

Hi Mary,

I'm about as patient as they come, having taught at the Community College
level for three years! <smile>

I think you were very close, except that you need to use the property name,
which is Duplex. Try the following in the Immediate Window (open by pressing
the control key and G at the same time, ie. Ctrl G):

?Application.Printer.Duplex

You will likely get an integer returned, such as 1, 2 or 3. Now try setting
this property in the Immediate Window. For example, type:

Application.Printer.Duplex=acPRDPVertical or
Application.Printer.Duplex=acPRDPHorizontal

Then interrogate the current setting once more with:

?Application.Printer.Duplex

Did you download the sample database named "ODC_Acc10_Printers.exe", 351 KB?

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> Thanks Tom.
>
[quoted text clipped - 13 lines]
>
> Thank you!!!

Reply to this Message

maryj - 30 Oct 2006 20:20 GMT

So, after entering that information into the Immediate Window, what do I do
with the results?

I typed in ?application.Printer.Duplex pressed Enter and it returned a 1
Then on the next line in the Immediate window - I typed in
Application.Printer.Duplex=acPRDPSimplex
Pressed Enter and on the next line typed in ?Application.Printer.Duplex
which again gave me a 1.

Yes, I downloaded the database, but unfortunately it isn't helping much.

Thanks for your help and patience!!

Signature

maryj

> Hi Mary,
>
[quoted text clipped - 43 lines]
> >
> > Thank you!!!

Reply to this Message

Tom Wickerath - 31 Oct 2006 09:18 GMT

Hi Mary,

The Immediate Window just provides a convenient method of running quick
tests, or printing stuff from a procedure using the Debug.Print statement. In
general, it is not intended to be used to make permanent changes.

The reason that you got a 1 the second time you issued the command:
?Application.Printer.Duplex

is that your initial setting was defaulted to acPRDPSimplex. So, when you
issued the command:

Application.Printer.Duplex=acPRDPSimplex

you were not changing anything. Thus the ?Application.Printer.Duplex
returned the same result. That's why I recommend trying it with the vertical
or horizontal constants.

I think one would just use the command within a procedure, such as the
following, however, I cannot properly test this because my printer does not
support duplex printing. Try something like this, for a command button named
"cmdPrint", after substituting a valid report name below:

Private Sub cmdPrint_Click()
On Error GoTo ProcError

Application.Printer.Duplex = acPRDPVertical

DoCmd.OpenReport "YourReportName"

ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501 'Report open cancelled
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdPrint_Click event procedure ..."
End Select
Resume ExitProc
End Sub

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> So, after entering that information into the Immediate Window, what do I do
> with the results?
[quoted text clipped - 8 lines]
>
> Thanks for your help and patience!!

Reply to this Message

maryj - 31 Oct 2006 14:20 GMT

Tom,

Here is the code I'm trying:

Private Sub Print_Report_Click()
Application.Printer.Duplex = acPRDPSimplex
DoCmd.OpenReport "rptBookStatus"
DoCmd.PrintOut acPrintAll
End Sub

The report prints, but it is still double sided.

Signature

maryj

> Hi Mary,
>
[quoted text clipped - 57 lines]
> >
> > Thanks for your help and patience!!

Reply to this Message

Tom Wickerath - 31 Oct 2006 23:43 GMT

Hi Mary,

Is there any chance that your rptBookStatus report is using a non-default
printer? I believe the command Application.Printer.Duplex = acPRDPSimplex
would only apply to the default printer.

Otherwise, I'm not sure what to suggest...

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> Tom,
>
[quoted text clipped - 7 lines]
>
> The report prints, but it is still double sided.

Reply to this Message

maryj - 01 Nov 2006 14:14 GMT

Tom,
The report is going to the default printer. Thanks for trying! If you think
of anything else to try, let me know!

Signature

maryj

> Hi Mary,
>
[quoted text clipped - 22 lines]
> >
> > The report prints, but it is still double sided.

Reply to this Message

Tom Wickerath - 01 Nov 2006 19:04 GMT

Hi Mary,

I tried adding duplex capability to the sample that Microsoft provides as a
download. In the process, I discovered a bug in their code for populating the
list box with the reports in the database. The list box in frmPrinter
displays 13 available reports. However, I count 14 reports total. The missing
report is "Summary of Sales by Year".

Close the form. Try importing a report from another database. Reopen the
form. All appears well, as you can see the report that you just imported in
the list box. Close the form again. Do a compact and repair of the database.
Reopen frmPrinter. Oops, why isn't the newly imported report showing up? At
least *now* I can see the "Summary of Sales by Year" report! My simple fix
for this problem is to change the method that is used to populate the list
box. Set the Row Source Type to a Table/Query, and set the Row Source to the
following query:

SELECT msysobjects.Name AS Reports
FROM msysobjects
WHERE (((msysobjects.Type)=-32764))
ORDER BY msysobjects.Name;

Comment out (or remove) the line of code in Form_Open that clears the
listbox, ie.:

' Clear lstSelectReport and cboPrinter lists
' before loading the current lists of reports
' and printers.
With Me
!cboPrinter.RowSource = ""
' !lstSelectReport.RowSource = ""
End With

Finally, remove the code that the author used in Form_Open to populate the
list box:

' Loop through reports and add each to
' lstSelectReport list box. This loops through the
' AllReports collection in inverse order instead
' of using a For Each...Next statement so
' that reports are listed in alphabetical order.
Me!lstSelectReport.RowSourceType = "Value List"
intCount = CurrentProject.AllReports.Count - 1
Do While intCount > 0
Me!lstSelectReport.AddItem _
CurrentProject.AllReports(intCount).Name
intCount = intCount - 1
Loop

On the duplex issue, after *many* trips to a shared network printer at work
that includes duplex capability, I've generated a lot of paper for the
recycle bin. I've only been able to get a report to print on both sides of a
single sheet of paper about 5~10 percent of the time. The other 90~95 %, the
darn report just prints out single sided. I was using a report that included
two pages, so that I'd know for sure if it printed as intended. Pretty
frustrating, to say the least.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> Tom,
> The report is going to the default printer. Thanks for trying! If you think
> of anything else to try, let me know!

Reply to this Message