@June7 post #4, I know, it's what I tried before I posted and read the article.
I am trying this, after reading the article, but there's a small problem,
The second record set isn't getting filtered (as best as I can tell).
I know lcnt2 should return 5 (for those with Ps=2),
but it's returning 36, the same as the original record set.
For some reason the filter isn't working in either of the two ways I tried (first commented out):
Code:
Public Sub tTwgReuseQuery()
'Try to reuse one query in another
Dim daDb As DAO.Database, wSql As String, daRsr1 As DAO.Recordset, daRsr2 As DAO.Recordset
Dim lcnt1 As Long, lcnt2 As LongLong
lcnt1 = 0: lcnt2 = 0
Set daDb = CurrentDb
wSql = "Select * from tbl_4_Entity WHERE NwResBeID=0102"
Set daRsr1 = daDb.OpenRecordset(wSql, dbOpenSnapshot)
If daRsr1.RecordCount <> 0 Then
daRsr1.MoveFirst
Do Until daRsr1.EOF
Debug.Print daRsr1!Ps,
lcnt1 = lcnt1 + 1
daRsr1.MoveNext
Loop
'daRsr1.Filter = "[Ps]=2"
Set daRsr2 = daRsr1
daRsr2.Filter = "[Ps]=2"
With daRsr2
If .RecordCount <> 0 Then
.MoveFirst
Do Until .EOF
'Debug.Print daRsr2!Nm1,
lcnt2 = lcnt2 + 1
.MoveNext
Loop
End If
End With
End If
Debug.Print lcnt1, lcnt2
daRsr2.Close
'daRsr1.Close
Set daRsr1 = Nothing
Set daRsr2 = Nothing
Set daDb = Nothing
End Sub
I think it's following the code sample from Daniel Pineault, however I'm not using a form's record set...?