asked on

Printer Selection for Report Printing in MS Access

I was assigned the task of getting reports that we print for our distributors to come out as PDFs for electronic distribution. Currently the Access application runs in one of three fashions:

Based upon an individual distributor (one record, one report, print previewed first)
Based upon a Territory (multiple records, based upon distributor location, sends reports consecutively to the printer)
Based upon Manager (same as based upon territory, this time filtered by Manager)

Here is the code:

'Populate the recordset with records from the temp table tbl_PrintReprtCriteria
Set dBase = CurrentDb
Set rRecord = CurrentDb.OpenRecordset("tbl_PrintReprtCriteria", dbOpenDynaset)
Do While Not rRecord.EOF

DoCmd.SetWarnings False
DoCmd.OpenQuery ("Delete tblProdNetPurch")
DoCmd.SetWarnings True

'Populates the form fields with the current record's information
Me.txtSoldCode = rRecord("Sold Code")
Me.txtSoldCompany = rRecord("Company Name")
Me.Terr = rRecord("Terr")
Me.Contact_Name = rRecord("Contact Name")
Me.txtDiscountCode = rRecord("Discount Code")

'No clue what this does, it was in the code when I was assigned the project
DoCmd.SetWarnings False
DoCmd.OpenQuery ("Append Item 1")
DoCmd.OpenQuery ("Append Item 2")
DoCmd.OpenQuery ("Append Item 3")
DoCmd.OpenQuery ("Append Item 4")
DoCmd.OpenQuery ("Append Item 5")
DoCmd.OpenQuery ("Append Item 6")
DoCmd.SetWarnings True

'(frReportType is a user selected option to print different reports)
'Print Regular Reports
If Me.frReportType.Value = 1 Then
If Mid(Me.txtDiscountCode, 2, 1) <> "N" Then
Select Case Left(Me.Terr, 3)
Case "FOR"
ReportName = "rptDistribSalesSummaryIntl"
Case "CAN"
ReportName = "rptDistribSalesSummaryCAN"
Case Else
If Me.Terr <> "MR22" Then
ReportName = "rptDistribSalesSummary"
Else
ReportName = "rptDistribSalesSummaryIntl"
End If
End Select
Else
Select Case Left(Me.Terr, 3)
Case "FOR"
ReportName = "rptDistribSalesSummaryIntl"
Case "CAN"
ReportName = "rptDistribSalesSummaryCAN"
Case Else
If Me.Terr <> "MR22" Then
ReportName = "rptDistribSalesSummary"
Else
ReportName = "rptDistribSalesSummaryIntl"
End If
End Select
End If

'Print Annual Reports
ElseIf Me.frReportType.Value = 2 Then
Select Case Left(Me.Terr, 3)
Case "FOR"
ReportName = "rptDistribSalesAnnualIntl"
Case "CAN"
ReportName = "rptDistribSalesAnnualCAN"
Case Else
If Me.Terr <> "MR22" Then
ReportName = "rptDistribSalesAnnual"
Else
ReportName = "rptDistribSalesAnnualIntl"
End If
End Select
End If

'************************************************************
'This code is what I used to print using the PDF printer, it worked the first time then caused problems. After the first time I ran this, each successive time, the program would try to print to the PDF Writer, indicating that the function ResetDefaultPrinter was not working. I took all this code from here: http://www.mvps.org/access/reports/rpt0011.htm and it works fine for printing to PDF...but I need to basically set the printer object to PDF or whatever other printer the user wants.
'*************************************************************

' If Me.chkPDF = True Then
' ChangeToAcrobat
' ChangePdfFileName "S:\PDC Documents\DBA Sales Reports\PDF Reports\" & Me.Contact_Name & ".pdf"
' strAttach = "S:\PDC Documents\DBA Sales Reports\PDF Reports\" & Me.Contact_Name & ".pdf"
' SendKeys strAttach, False
' SendKeys "{Enter}", False
' DoCmd.OpenReport ReportName
' 'DoCmd.PrintOut
' 'DoCmd.Close
' ResetDefaultPrinter
' ElseIf Me.chkPDF = False Then
DoCmd.SetWarnings False
DoCmd.OpenReport ReportName, sPreview
DoCmd.SetWarnings True
' End If
rRecord.MoveNext
Loop

When i commented out the If statement (the one that checks if the PDF box has been checked and then uses the PDF writer), the program ran once again. Basically I need a way to, when the user clicks on cmdReport, rather than just going ahead with the Do loop, i would like to set the printer to PDF, Laserjet, whatever, then go ahead with printing. All this development is being done in Access 2000.

HELP PLEASE!!

ASKER CERTIFIED SOLUTION

joekendall

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

strikerx8

ASKER

Thanks for that link. That describes how VBA handles the printer object, (I.E. changing the default printer in code), however I really would like to allow users to select a printer, which means I need a combo box or list box that contains a list of all available printers. After the user selects it, I can set the PrtDevMode and PrtDevName properties based upon their selection.

How can I get a list of available printers and display them as option values in a combo box?

joekendall

If you downloaded the DefaultPrt.zip file which is contained in the link I provided, it will show a combobox with the list of printers. You can emulate that in your app.

Joe

strikerx8

ASKER

https://www.experts-exchange.com/questions/20688183/Get-Installed-Printer-list-and-Set-Printer-in-Acccess-2000.html

This is exactly what I'm trying to do....his accepted answer link doesn't work anymore...

strikerx8

ASKER

I think that the problem with the link that you sent me is that it is all programmed in Access 95. Does Access 2000 handle printers in the same fashion, using the same names?

I actually had all those modules already in my DB because I used the site you sent as reference when I needed to print to PDF automatically. Basically I'd rather just populate a combo box with a list of printers and then set the default printer to the selected printer. After printing all reports, I'd like to change the default printer back to whatever the user had before running the reports.

joekendall

It should convert to 2000 fine. Where you able to use the example and populate the combobox with the list of printers?

Joe

strikerx8

ASKER

I believe that it converted ok to Access 2000, however the way that the modules handle changing the printer is less than efficient. Basically that code goes into the win.ini file, then finds out the default printer and assigns it's characteristics to the global variable "drexisting". It then uses my hard coded constants (DeviceName="Acrobat PDF Writer", DeviceDriver="Acrobat PDF Writer", DevicePort="LPT1;") and creates the object "dr" with those characteristics, then writes those values into the win.ini file, therefore changing the default printer to Acrobat's PDF writer. Basically what I'd like to be able to do is populate the combo box with ALL available printers (no I haven't been able to use their example yet, but have tried) and then if possible, rather than change the default printer to something different and then have to bother with changing it back (I would like to avoid writing to the win.ini file if possible, I don't like messing with it unless it's totally necessary, too much room for error), I'd rather change the prtDevName property of the REPORT to something temporarily, that way the report is set to print to a laser printer usually, but changes based upon the users selection. When I go into Page Setup, you can set it to use the system default or a specific printer, basically I just need to change the specific printer, but need to do it in code based upon the user's selected printer.

Thanks for the help so far....I hope we can get through this....

joekendall

I will see what I can find. When I look at the example, it is reading from the WIN.INI file. If you can use the Printer Object itself in Access 2000, it should be much easier to do.

Joe

strikerx8

ASKER

That's the issue that I've been having. I have a VB programmer on staff at work, and he's just baffeled by the fact that the Printer object doesn't exist in VBA for Access 2000. He said the same thing you did..."If you can use the Printer Object itself...." Things could be easier. Thanks for your help so far, I'll be working on this over the weekend and will pick back up Monday.

strikerx8

ASKER

So basically I believe that I've found a way sort of around this problem. I still cannot figure out exactly how to populate a combo box with the available printers, however, by setting each report to print to the system default printer, then setting up a flag inside of my code, I seem to have worked out the problem. However, all I'm doing now is what I was doing before, except that instead of changing the default printer to the PDF Writer and then back each time I run through the recordset loop (meaning that I was setting and resetting the default printer upwards of 100 times depending on the number of records), now I check to see if a checkbox has been selected in the form, and if so, set the system default printer to the PDF Writer. After it exits the loop, it then checks if the printer has been changed (here's where I used the flag), and if it HAS, I change it back. This causes it to only change and reset once no matter how many records are in the recordset.

This seems to work, but I would rather do it the right way. Also, now sometimes the print dialog box will come up indicating that the report is being converted to PDF, but it never actually prints. I viewed the directory that the files are supposed to be being saved in, and the PDF file appears when the dialog comes up, but then quickly dissappears. It's as if the print command is being sent, but never actually being done. I ran this report printing process on 105 records, and only 4 came out the other side converted to PDF. This is not a PDF related problem, because when I go to print hard copies, the same thing occurs. The system prints (or seems to send the command at least) all 105 records, but only 4 hard copies come out of the printer. It's like 101 commands never leave the system and go to the printer.

Any help that you could offer would be much appreciated. You've been more than helpful so far....HELP PLEASE!!!