How to use records selected in a recordset as the record source for the Detail section of a report.
Q: Mark Jackson writes to ask if records selected in a recordset can be used as the record source for the Detail section of a report.
A: I think the best way to do this would be to use VBA code to create a saved query on-the-fly, using whatever filtering is needed, and then assign that query as the record source of the report (or possibly of a subreport in the report’s Detail section). Here is code I use to create saved queries in code as needed (I keep this code segment in a text file, so I can import it into a database as needed, and modify it as needed for the specific object and field names):
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strQuery As String
Dim strSQL As String
Dim lngCount As Long
strInventoryCode = Me![InventoryCode]
strQuery = “qryTemp”
Set dbs = CurrentDb
strSQL = “SELECT * FROM tblInventoryItemsComponents WHERE ” _
& “[InventoryCode] = ” & Chr$(39) & strInventoryCode & Chr$(39) & “;”
Debug.Print “SQL for ” & strQuery & “: ” & strSQL
lngCount = CreateAndTestQuery(strQuery, strSQL)
Debug.Print “No. of items found: ” & lngCount
If lngCount = 0 Then
strPrompt = “No records found; canceling”
strTitle = “Canceling”
MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
GoTo ErrorHandlerExit
End If
=========================
Public Function CreateAndTestQuery(strTestQuery As String, _
strTestSQL As String) As Long
On Error Resume Next
‘Delete old query
Set dbs = CurrentDb
dbs.QueryDefs.Delete strTestQuery
On Error GoTo ErrorHandler
‘Create new query
Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
‘Test whether there are any records
Set rst = dbs.OpenRecordset(strTestQuery)
With rst
.MoveFirst
.MoveLast
CreateAndTestQuery = .RecordCount
End With
ErrorHandlerExit:
Exit Function
ErrorHandler:
If Err.Number = 3021 Then
CreateAndTestQuery = 0
Resume ErrorHandlerExit
Else
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Function
This technique is used in the sample database for this AW, Merge Recordset to Word (AA 168).mdb, so you can see how it is used in a real database.
