Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim SQL_Select As String
SQL_Select = "Select * from tbl1"
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL_Select)
rs.Filter = "date1 < #1/1/24# "
Set rs2 = rs.OpenRecordset
rs.MoveLast
rs2.MoveLast
Debug.Print rs.RecordCount, rs2.RecordCount
MyExit:
rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
Set db = Nothing
End Sub
Suppose you could also set the 1st recordset as global.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim SQL_Select As String
SQL_Select = "Select * from tbl1"
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL_Select)
rs.Filter = "date1 < #1/1/24# "
Set rs2 = rs.OpenRecordset
rs.MoveLast
rs2.MoveLast
Debug.Print rs.RecordCount, rs2.RecordCount
MyExit:
rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
Set db = Nothing
End Sub
Suppose you could also set the 1st recordset as global.
I made the changes you suggested and it works fine. Thanks.
Once I find some time I will do it the way it was suggested.
1) Get only one recordset, the one with all the appointments
2) Maybe pass it as a parameter to the form or use a public recordset
3) Use a filter on the form to show "All Patient Appointments" or "Future Patient Appointments"
The appointments associated with a patient may range from 1 to 20, in any case not many.
I would be interested if the filter recordset provides any efficiency. In my experience it is faster to simply open the new filtered recordset as MS states.
I would be interested if the filter recordset provides any efficiency. In my experience it is faster to simply open the new filtered recordset as MS states.
I have tested the Filter option with the large db on my pc and it is very fast. I need to test it on the actual network and will let you know.
As you commented a patient's appointments is a very small number, so at first sight,if I had a choice of filtering say 10 records Vs querying 500000 records, I would choose the former.
My understanding is it is not that simple. In that It does not directly filter the recordset but just creates a new filtered connection. Maybe I am incorrect in that interpretation. @Galaxiom reports good performance.
I searched all over for a good example of filtering a recordset then refiltering and want the most efficient way of handling the following: I have query returning all people as level name 10 smith 20 jones 20 jackson 40 simpson so my db.OpenRecordSet ("qryAllPeople") will give me...