You are here:

Business Software/Printing a filtered datasheet

Expert: Scottgem - 3/11/2009

Question
In an Access 2007 database, is there a simple way in VBA to determine a (subform) datasheet's data (as filtered and ordered by the end user) in order to print just their selection via a print button on the main form?

Answer
If there is a filter being applied to a subform, you can use the Filter property in the OpenReport method like so:

DoCmd.OpenReport "reportname",,,Me.Filter

However, this may not pick up the filtering that is performed by the parent-child link between the main form and subform so you may need to use:

DoCmd.OpenReport "reportname",,,Me.Filter & " AND [foreignkey] = " & Forms!mainformname!keycontrolname

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Questioner's Rating

Rating(1-10)	Knowledgeability = 10	Clarity of Response = 10	Politeness = 10
Comment	Thank you for your help. My final code works a treat Dim db As Database Dim qry As QueryDef Dim SQLstr As String Set db = CurrentDb() Set qry = db.QueryDefs("qryDatasheetPrintPreview") If Not IsNull(Forms![frmEntries].[sfrmTransactions].Form.RecordSource) And Forms![frmEntries].[sfrmTransactions].Form.RecordSource <> "" Then SQLstr = "SELECT * FROM " & Forms![frmEntries].[sfrmTransactions].Form.RecordSource End If If Not IsNull(Forms![frmEntries].[sfrmTransactions].Form.Filter) And Forms![frmEntries].[sfrmTransactions].Form.Filter <> "" Then SQLstr = SQLstr & " where " & Forms![frmEntries].[sfrmTransactions].Form.Filter End If If Not IsNull(Forms![frmEntries].[sfrmTransactions].Form.OrderBy) And Forms![frmEntries].[sfrmTransactions].Form.OrderBy <> "" Then SQLstr = SQLstr & " order by " & Forms![frmEntries].[sfrmTransactions].Form.OrderBy End If qry.SQL = SQLstr DoCmd.OpenQuery "qryDatasheetPrintPreview", acViewNormal, acReadOnly Set qry = Nothing Set db = Nothing