Solved

Printer Selection for Report Printing in MS Access

Posted on 2003-10-23
10
1,340 Views
Last Modified: 2008-03-10
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!!

0
Comment
Question by:strikerx8
  • 6
  • 4
10 Comments
 
LVL 11

Accepted Solution

by:
joekendall earned 250 total points
Comment Utility
Did you use the code that is in the link that shows how to change the default printer? If not, I believe this will help with the printer selection.

http://www.mvps.org/access/reports/rpt0009.htm

Joe
0
 

Author Comment

by:strikerx8
Comment Utility
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?
0
 
LVL 11

Expert Comment

by:joekendall
Comment Utility
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
0
 

Author Comment

by:strikerx8
Comment Utility
http://www.experts-exchange.com/Databases/MS_Access/Q_20688183.html

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

Author Comment

by:strikerx8
Comment Utility
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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 11

Expert Comment

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

Joe
0
 

Author Comment

by:strikerx8
Comment Utility
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....

0
 
LVL 11

Expert Comment

by:joekendall
Comment Utility
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
0
 

Author Comment

by:strikerx8
Comment Utility
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.
0
 

Author Comment

by:strikerx8
Comment Utility
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!!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now