How to filter a table-type recordset.
Q: Mark Jackson writes: “Does anyone have a very complete example of how to open a table type recordset based on a table in the same database? I can’t seem to find what I am missing in my code. I was able to get values from a recordset and use them, but couldn’t seem to narrow record set records to starting date on form and later. “
A: Here is the standard code for opening a table-type recordset:
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblBackupInfo”, dbOpenTable)
However, I think you actually need a query here, to filter by the selected date. You can create a query in code, with the date from the form as a criterion. Here is some sample code that you can adapt for your needs:
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
