Where do I put Access vba code to only fire when physically printing?

Question

I have some code that I want to fire when the user physically prints the report. Not when Print Previewing, etc, but only when sending to the printer. The user needs to be able to pull up the report and view it, then if they decide to print, the vba code will take over and write some info to a different table than is being used to generate the report. I was hoping not to have to place a Print button on the actual report (even though I know I can hide it for the print), so I was wondering if I could somehow trap the Print dialog instead.

Has anyone ever had any luck doing so?

Answer 1

After much consideration, I think the best way to accomplish this is by identifying the Active Window text during the report Page event. During a print preview, this text will be the name of the database itself, something like "Microsoft Access - DatabaseName : Database (Access 2003). During a real printing operation, the active window will be "Printing"

I credit most of the code as coming from this source.

Declare Function GetActiveWindow Lib "user32" () As Long
Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
   (ByVal Hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private Sub Report_Page()
On Error GoTo PrintError

    Dim strCaption As String
       Dim lngLen   As Long

       ' Create string filled with null characters.
       strCaption = String$(255, vbNullChar)
       ' Return length of string.
       lngLen = Len(strCaption)

       ' Call GetActiveWindow to return handle to active window,
       ' and pass handle to GetWindowText, along with string and its length.
       If (GetWindowText(GetActiveWindow, strCaption, lngLen) > 0) Then
          ' Return value that Windows has written to string.
          ActiveWindowCaption = strCaption
       End If

    If ActiveWindowCaption = "Printing" Then

        '
        ' Special activity goes here.
        '

    End If

    Exit Sub

PrintError:
    ' Just in case

End Sub