Copy Recordset to another Recordset (1 Viewer)

[JohnPapa]

I have Recordset rst1 which is the result of a query

dim strSQL as string
strSQL = "SELECT tblAppointment.lngAppointID, tblAppointment.lngPatientID, tblAppointment.dtmAppointDate, tblAppointment.dtmAppointTime, tblAppointment.txtAppointDesc, tblAppointment.chkKeptAppointYesNo, " _
& " tblAppointment.lngServiceID, tblDoctor.txtSurnameName, tblAppointment.blnSMSWasSent, tblAppointment.lngAppointmentStatus, tblAppointment.txtAppointmentStatus, tblCustomer.txtSurname, tblCustomer.txtName " _
& " FROM tblCustomer INNER JOIN (tblDoctor INNER JOIN tblAppointment ON tblDoctor.lngDoctorID = tblAppointment.lngAppointDoctorID) ON tblCustomer.lngCustomerID = tblAppointment.lngPatientID " _
& " WHERE (((tblAppointment.lngPatientID) = " & Forms!frmSchedule!subAppointmentNew.Form!cbolngPatientID & ") ) " _
& " ORDER BY tblAppointment.dtmAppointDate DESC, tblAppointment.dtmAppointTime;"
set rst1 = new ADODB.Recordset
rst1.open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

I would like to create rst2, a copy of rst1, where

tblAppointment.dtmAppointDate > Date

without rerunning the query above.

I want to end up with rst1 and rst2, two distinct Recordsets.

I have tried using Clone

Set rst2 = rst1.clone

but this will not create two distinct Recordsets

 

[moke123]

Seems to me you want rs2 as a subset of rs1. Have you considered something like a DAO rs1 and then set rs2 as a filtered subset of rs1?

 

[CJ_London]

if you are using ado, you need to set the recordset to itself after applying the filter e.g.

set rst2=rst1
rst2.filter="dtmAppointDate > Date()"
set rst2=rst2

or you could disconnect rst1 after populating it. on my phone but think the code would be

rst1.open strSQL,........
rst1.connection=nothing

depends on what you are doing with the data

 

[cheekybuddha]

I would like to create rst2, a copy of rst1, where

without rerunning the query above.

Why not re-run the query? It may be simpler.

 

[JohnPapa]

Why not re-run the query? It may be simpler.

The reason why I do not want to rerun the query is that it can take a lot of time. This is the way it works now.

 

[JohnPapa]

Seems to me you want rs2 as a subset of rs1. Have you considered something like a DAO rs1 and then set rs2 as a filtered subset of rs1?

Yes, it is a subset. The main question is how to create rs2 without affecting rs1.

 

[JohnPapa]

if you are using ado, you need to set the recordset to itself after applying the filter e.g.

set rst2=rst1
rst2.filter="dtmAppointDate > Date()"
set rst2=rst2

or you could disconnect rst1 after populating it. on my phone but think the code would be

rst1.open strSQL,........
rst1.connection=nothing

depends on what you are doing with the data

It does not work, when I try to

Set Me.Recordset = rst2

It says Run-time error 5, Invalid procedure call or argument.

This works when rst2 is the result of a a query with the dtmAppointDate > Date()

 

[cheekybuddha]

The reason why I do not want to rerun the query is that it can take a lot of time.

Do you have hundreds of thousands of records in your table?

Your query is not particularly complicated.

Do you have the relationships explicitly defined with Referential Integrity?

If not, have you indexes defined on the following fields in tblAppointment:
lngAppointDoctorID
lngPatientID
dtmAppointDate
dtmAppointTime

?

 

[JohnPapa]

To reiterate, I want to create rst2 based on rst1 with a filter parameter and both rst1 and rst2 need to be available.

 

[cheekybuddha]

Also, are you using Access as the backend, or a different RDBMS?

 

[moke123]

Set Me.Recordset = rst2

What about just filtering the form?

Probably help if you give a little more detail into what your trying to do

 

[JohnPapa]

Do you have hundreds of thousands of records in your table?

Your query is not particularly complicated.

Do you have the relationships explicitly defined with Referential Integrity?

If not, have you indexes defined on the following fields in tblAppointment:
lngAppointDoctorID
lngPatientID
dtmAppointDate
dtmAppointTime

?

The database is a simple ACE db with over 500 000 records, just the Appointments. It is used on a simple network (no dedicated server, just a synology) with 15 pcs. We have another version of the software which runs on SQL Server on Azure. I am trying not to run the same query twice.

All 4 fields you mention are indexed.

 

[JohnPapa]

Also, are you using Access as the backend, or a different RDBMS?

As I mentioned before, in the specific case I am using ACE.

 

[cheekybuddha]

OK, why are you using ADO, which is almost always slower than DAO?

Do you need disconnected recordsets?

 

[JohnPapa]

What about just filtering the form?

Probably help if you give a little more detail into what your trying to do

The problem has to do with "All Patient Appointments" (rst1) and the "Future Patient Appointments" (rst2)

I could just always bring to the form "All Patient Appointments" (rst1) and filter the "Future Patient Appointments", BUT it is set up right now to work with 2 recordsets and would involve a lot of work to change.

The requirement is very clear, create both rst1 and rst2 without running the query twice.

 

[JohnPapa]

OK, why are you using ADO, which is almost always slower than DAO?

Do you need disconnected recordsets?

No I do not need disconnected recordsets. DAO does not make a big difference.

 

[cheekybuddha]

My suggestion would be more along the lines of what Moke suggested.

Dispense with the recordsets altogether.

Use your query (without the WHERE clause) as the RecordSource of your form.

Open your form passing Forms!frmSchedule!subAppointmentNew.Form!cbolngPatientID as the Where argument of the DoCmd.OpenForm

Then, use Me.Filter = "dtmAppointDate > " & Format(Date, "\#yyyy\-mm\-dd\#")
Turn it on/off using:
Me.FilterOn = True ' or False as required

 

[JohnPapa]

My suggestion would be more along the lines of what Moke suggested.

Dispense with the recordsets altogether.

Use your query (without the WHERE clause) as the RecordSource of your form.

Open your form passing Forms!frmSchedule!subAppointmentNew.Form!cbolngPatientID as the Where argument of the DoCmd.OpenForm

Then, use Me.Filter = "dtmAppointDate > " & Format(Date, "\#yyyy\-mm\-dd\#")
Turn it on/off using:
Me.FilterOn = True ' or False as required

Agreed, what you mention will work and thanks for pointing it out.
BUT is there a way to start from rst1 and create rst2 = rst1 with a parameter?

 

[moke123]

BUT is there a way to start from rst1 and create rst2 = rst1 with a parameter?

yes, use the rs.filter property

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.

 

[cheekybuddha]

I was surprised your original method did not work, but apparently it's 'a thing'!

See here on SatckOverflow