Print Active Form Record
When a print statement is issued on a FORM, all records in the Form's recordset will be printed. To only print the active record in a form requires special code to filter just the record(s) that you want to print. There are two methods to accomplish this: the Recordset and RecordsetClone properties. This page shows 3 examples of methods to accomplish this goal.
Program Code
Private Sub cmdPrintForm_Click1()
' *********************************************************************
' Print Only the Active Record In A Form
' *********************************************************************
Dim rst As DAO.Recordset
Dim lngClientContactID As Long
On Error GoTo cmdPrintClientContact_Click_Err
' *********************************************************************
' Save Primary Key of Current Record
' *********************************************************************
lngClientContactID = Me.CCClientContactID
' *********************************************************************
' Filter To The One Record I Want To Print on the Form
' (The Current Active Record) - The Record Count on the Form
' Will Now Only Show 1 Record
' *********************************************************************
Me.Detail.BackColor = 16777215 'White
Me.Filter = "CCClientContactID = " & lngClientContactID
Me.FilterOn = True
' *********************************************************************
' Print the One Filtered Form Record
' *********************************************************************
DoCmd.SelectObject acForm, "frmClientContact", True
DoCmd.RunCommand acCmdPrint
DoCmd.SelectObject acForm, Screen.ActiveForm.Name, False
' *********************************************************************
' Turn Off the Filter And Show All Records in the Form
' The Record Count Goes Back To Normal
' However, The Position of the Record in the form changes to
' The First Record in the Dataset And Not The Record We Printed
' *********************************************************************
Me.Detail.BackColor = 14151142 'Green
Me.Filter = ""
Me.FilterOn = False
' *********************************************************************
' Using The Form's Recordset, Locate The Printed Record So The
' Form Returns To Its Previous Position
' *********************************************************************
Set rst = Me.Recordset
rst.FindFirst "CCClientContactID = " & lngClientContactID
If rst.NoMatch Then
MsgBox "Record not found"
End If
' *********************************************************************
' Note That I can't Close rst because the form will show all ????
' *********************************************************************
cmdPrintClientContact_Click_Exit:
Exit Sub
cmdPrintClientContact_Click_Err:
MsgBox Error$
Resume cmdPrintClientContact_Click_Exit
End Sub
Private Sub cmdPrintForm_Click2()
' *********************************************************************
' Print Only the Active Record In A Form
' *********************************************************************
Dim rst As DAO.Recordset
Dim lngClientContactID As Long
Dim bkmBookmark As Variant
On Error GoTo cmdPrintClientContact_Click_Err
' *********************************************************************
' Using The Form's RecordsetClone, Locate The Printed Record So The
' Form Returns To Its Previous Position After The Filter is Removed
' *********************************************************************
lngClientContactID = Me.CCClientContactID
Set rst = Me.RecordsetClone
rst.FindFirst "CCClientContactID = " & lngClientContactID
If rst.NoMatch Then
MsgBox "Record not found"
rst.Close
Set rst = Nothing
Exit Sub
End If
bkmBookmark = rst.Bookmark
' *********************************************************************
' Save Primary Key of Current Record
' *********************************************************************
lngClientContactID = Me.CCClientContactID
' *********************************************************************
' Filter To The One Record I Want To Print on the Form
' (The Current Active Record) - The Record Count on the Form
' Will Now Only Show 1 Record
' *********************************************************************
Me.Detail.BackColor = 16777215 'White
Me.Filter = "CCClientContactID = " & lngClientContactID
Me.FilterOn = True
' *********************************************************************
' Print the One Filtered Form Record
' *********************************************************************
DoCmd.SelectObject acForm, "frmClientContact", True
DoCmd.RunCommand acCmdPrint
DoCmd.SelectObject acForm, Screen.ActiveForm.Name, False
' *********************************************************************
' Turn Off the Filter And Show All Records in the Form
' The Record Count Goes Back To Normal
' However, The Position of the Record in the form changes to
' The First Record in the Dataset And Not The Record We Printed
' *********************************************************************
Me.Detail.BackColor = 14151142 'Green
Me.Filter = ""
Me.FilterOn = False
' *********************************************************************
' Position the form record to its original active location
' *********************************************************************
Me.Bookmark = bkmBookmark
' *********************************************************************
' Close The Recordset Clone
' *********************************************************************
rst.Close
Set rst = Nothing
cmdPrintClientContact_Click_Exit:
Exit Sub
cmdPrintClientContact_Click_Err:
MsgBox Error$
Resume cmdPrintClientContact_Click_Exit
End Sub
Private Sub cmdPrintForm_Click3()
' *********************************************************************
' Print Only the Active Record In A Form
' *********************************************************************
Dim frm As Form
Dim rst As DAO.Recordset
On Error GoTo cmdPrintClientContact_Click_Err
' *********************************************************************
' Save Primary Key of Current Record
' *********************************************************************
Set frm = Screen.ActiveForm
lngClientContactID = Me.CCClientContactID
' *********************************************************************
' Filter To The One Record I Want To Print on the Form
' (The Current Active Record) - The Record Count on the Form
' Will Now Only Show 1 Record
' *********************************************************************
frm.Detail.BackColor = 16777215 'White
frm.Filter = "CCClientContactID = " & lngClientContactID
frm.FilterOn = True
' *********************************************************************
' Print the One Filtered Form Record
' *********************************************************************
DoCmd.PrintOut acSelection
DoEvents 'Optional
' *********************************************************************
' Turn Off the Filter And Show All Records in the Form
' The Record Count Goes Back To Normal
' However, The Position of the Record in the form changes to
' The First Record in the Dataset And Not The Record We Printed
' *********************************************************************
frm.Detail.BackColor = 14151142 'Green
frm.Filter = ""
frm.FilterOn = False
' *********************************************************************
' Using The Form's Recordset, Locate The Printed Record So The
' Form Returns To Its Previous Position
' *********************************************************************
Set rst = Me.Recordset
rst.FindFirst "CCClientContactID = " & lngClientContactID
If rst.NoMatch Then
MsgBox "Record not found"
End If
' *********************************************************************
' Note That I can't Close rst because the form will show all ????
' *********************************************************************
cmdPrintClientContact_Click_Exit:
Exit Sub
cmdPrintClientContact_Click_Err:
MsgBox Error$
Resume cmdPrintClientContact_Click_Exit
End Sub