HOW TO: Print a Particular Instance of a Report in Access 2002 (304518)

SUMMARY

In Microsoft Access, you can create new instances of heavyweight Form and Report objects. You can do this by using the New keyword. You can use this method to open multiple instances of the same report. These instances may have different records. When you want to print a report instance, however, the DoCmd.OpenReport method always prints the base class of the report, and not a particular instance that you have created.

This article shows you how to use the Win32 API and the DoCmd.PrintOut method to programmatically print a particular instance of a report. The example uses the Alphabetical List of Products report from the Northwind sample database. The example will first have you add a text box that will hold the instance number to the report header of this report. It will then show you how to write the code that does the following:

• Creates three instances of the report.
• Prompts for an instance number.
• Prints the instance selected.

back to the top

Steps to Create This Example

1. Start Access.
2. On the Help menu, point to Sample Databases, and then click Northwind Sample Database.
3. Add an unbound text box to the report header. Name the text box txtInstance.
4. Change the label caption of the text box to Instance:.
5. On the Edit menu, click Select Report.
6. Add the following code to the OnOpen event procedure:

   Me.txtInstance = x
   					

7. Save and then close the report.
8. Open the module Startup in Design view.
9. Add the following line to the Declarations section:

   Public x As Integer
   					

10. Save and then close the module.
11. Create a new form. Name it Form1.
12. Add a command button to the form. Name it Command0.
13. On the View menu, click Code, and then add the following code:

    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, _
    ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    
    Private Const WM_ACTIVATE = &H6
    Private col As VBA.Collection
    
    Private Sub Command0_Click()
        'Set x to 1 for the example.
        x = 1
        'Assumes Alphabetical List of Products HasModule Property = True
        Dim r As [Report_Alphabetical List of Products]
        Dim intWhichReport As Integer
        Dim result As Long
        Dim i As Integer
        Static Values(3) As Double  ' Set up array.
        Set col = Nothing
        Set col = New VBA.Collection
        
        'Fill the custom collection with 3 instances of the Alphabetical
        'List of Products report, and make them all visible.
        For i = 1 To 3
            Set r = New [Report_Alphabetical List of Products]
            r.Visible = True
            'Add 1 to x. 
            'Report open event will use new value.
            x = x + 1
            col.Add r
            'Store the handle in the array.
            Values(i) = col.Item(i).hwnd
        Next
    
        'Ask the user for which instance (1 to 3) of the report to print.
        On Error Resume Next
        intWhichReport = InputBox("Which report instance (1-3) do you want to print?")
        
        'Get the number entered by the user, and grab the appropriate instance
        'from the custom collection. Then get its hWnd property and pass it to
        'the SendMessage function, so that the proper report instance becomes
        'the active report. Then use DoCmd.PrintOut to print that instance.
        If Err.Number = 0 Then
            result = SendMessage(Values(intWhichReport), WM_ACTIVATE, True, True)
            DoCmd.PrintOut
            
        End If
      
    End Sub
    					

14. Open the Form1 form in Form view, and then click Command0. Type the number of the instance that you want to print, and then click OK.
    
    Note that the Instance number in the report header on the printed report matches the instance that you selected.

back to the top