[RESOLVED] Printing in MS Access

[OllieVB]

Hi there,

I've got a database that requires some reports to be sent to different printers. As this database is to be run on a couple of different systems with quite different printers installed, I figured the best way to do this was to use the common dialog box to set the printer prior to the report being printed - The proper way to do it! However, I'm new to common dialog boxes and although I can handle the open/save modes with ease, I don't know where to start with the printer mode and currently, althought the correct box is coming up, the report is still printing to the printer that was selected from the menu when I designed it.

So I need to do the following:

1. Select the printer from those available on the system
2. Be able to set the number of copies
3. Be able to select the quality, paper source, etc. as usual
4. Disable the page selection (as the reports are preformatted as single pages)
5. Print the report using the above settings (Didn't need to say that I suppose!)

Here's my code so far:

Private Sub Print_profile_Click()
'On Error GoTo Exit_Print_profile_Click
    
    Dim stDocName As String
    
    'Save any changes made to the record prior to printing
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    'Printer options
    CMDialog4.Action = 5
    
    'WHAT DO I PUT HERE?
    
    stDocName = "Client Profile"
    DoCmd.OpenReport stDocName, acNormal
 
Exit_Print_profile_Click:
End Sub

Can anyone help please?

[SonicBoomAu]

I found that access report printing is fun to say the least.

This is what I do.

' Need to Select the Printer
        Dim ps As New Printing.PrinterSettings
        PrintDialog1.PrinterSettings = ps
        ' Capture the Original Printer
        strDefaultPrinter = Me.PrintDialog1.PrinterSettings.PrinterName
        ' Need to Select the Printer
        PR = Me.PrintDialog1.ShowDialog()
        ' Capture the required printer
        strSelectedPrinter = Me.PrintDialog1.PrinterSettings.PrinterName

Change the default printer to the required printer

ChangeDefaultPrinter(strSelectedPrinter)

Public Function ChangeDefaultPrinter(ByVal prtName As String) As Boolean
 
        Dim s As Microsoft.Win32.RegistryKey = Microsoft.Win32.Registry.CurrentUser
        Dim m As Microsoft.Win32.RegistryKey
 
        m = s.OpenSubKey("Software")
        m = m.OpenSubKey("Microsoft")
        m = m.OpenSubKey("Windows NT")
        m = m.OpenSubKey("CurrentVersion")
        m = m.OpenSubKey("Windows", True)
        m.SetValue("Device", prtName & ",winspool,FILE:")
 
        m.Flush()
        m.Close()
        s.Close()
 
 
    End Function

Print the Access Report

' Start a new instance of Access for Automation:
        Acc = New Access.ApplicationClass
 
        ' Open the Database in shared mode:
        Acc.OpenCurrentDatabase(strDatabaseName, False)
 
        ' Select the report name in the database window and give focus
        ' to the datbase window.
        Acc.DoCmd.SelectObject(Access.AcObjectType.acReport, strReportName, True)
 
        'Print the report
        Acc.DoCmd.OpenReport(strReportName, Access.AcView.acViewNormal)
 
        ' Quit Access and release object:
        On Error Resume Next
        Acc.Quit(Access.AcQuitOption.acQuitSaveNone)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(Acc)
        Acc = Nothing

Change the printer back to the default

ChangeDefaultPrinter(strDefaultPrinter)

Hope this makes sense and helps.

[OllieVB]

Hmmm. Not quite what I was expecting and it seems rather complicated but it may be that's the way it has to be done. I would have thought there was a simpler way to instruct access to direct the report to the selected printer.

Anyone else have any ideas?

[OllieVB]

Finally got this one sorted, thanks to some valuable input from dannymking who gave me the idea and the command to look up in msdn. Here's my code (same in each report)...

Private Sub Report_Activate()
On Error GoTo Error_Report
 
    Dim P As Boolean
    P = [Forms]![Stationery]![Print?]
 
    If P Then
        DoCmd.RunCommand acCmdPrint
        DoCmd.Close
    End If
    
Error_Report:
    If P Then
        DoCmd.Close
    End If
End Sub

The reference setting P is just a simple checkbox that's hidden on the form carrying my print and preview buttons and set or cleared by whichever button is clicked. This method means that the system default is not altered and full printing control is given to just the report currently being processed. Hope this may help others.

Thanks.