Recordset from a recordset

[RD]

Hi all,

Just a quick one ... I've never had to do anything like this before.

Set rs1 = db.OpenRecordset(SQLstatement, dbOpenDynaset)
rs1.Filter = strFilter
Set rs2 = rs1.OpenRecordset

Assuming the SQL statement opens an editable recordset, if I try to edit rs2
will it actually edit the record in the table that the SQL statement is based
on?

rs2.Edit
rs2.Fields(fieldname) = newvalue
rs2.Update

Or, should I go back and perform the edit on rs1?

Don't want to go to far down the wrong path.

Thanks,
RD

 

[Ken Higgins]

Hi all,

Just a quick one ... I've never had to do anything like this before.

Set rs1 = db.OpenRecordset(SQLstatement, dbOpenDynaset)
rs1.Filter = strFilter
Set rs2 = rs1.OpenRecordset

Assuming the SQL statement opens an editable recordset, if I try to edit rs2
will it actually edit the record in the table that the SQL statement is based
on?

rs2.Edit
rs2.Fields(fieldname) = newvalue
rs2.Update

Or, should I go back and perform the edit on rs1?

Don't want to go to far down the wrong path.

Thanks,
RD

 

[Ken Higgins]

I am not an MVP but ...have struggled with
the same question

Yes you will be working on the same record set. as yo udepicted

If you use filtering on a single field alot, then you might
consider using ADO.

DAO uses multiple recordset type objects which rewference the
same base recordset. I think that is a bit confusing.
So here is a template type program using ADO that might be a bit easier
to consider and utilize.

--------------------
Public Sub Test_Filter()
Dim rst As ADODB.Recordset
Dim strFilter As String
Dim vOldValue As Variant

'you'll be operating on the same recordset even though its filtered.
'DAO does the same thing except you have to deal with 2 or more
recordsets.
'ADO does it cleaner. (MY Opinion)

Set rst = New ADODB.Recordset
With rst

.ActiveConnection = CurrentProject.AccessConnection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.SOURCE = "[Test_Table]" ' you could use an sql statement here too
.Open
.MoveLast

Debug.Print "Total Records (No Filter) = " & Format(.RecordCount,
"#,##0")

End With

'assuming field1 is a text field
' (note the single quotes around the text field value)

strFilter = "[Field1]='2469-0005'"
rst.Filter = strFilter

Debug.Print "Total Records (With Filter) = " & Format(rst.RecordCount,
"#,##0")

'<Add Code to Manipulate records / Change Field Values here >

'now kill the filter
rst.Filter = ""

Debug.Print "Total Records(Filter set to zero length string) = & _
Format(rst.RecordCount, "#,##0")

rst.Close
Set rst = Nothing

MsgBox "End of Job"

End Sub

Happy Coding!

 

[Alex Dybenko]

Hi,
in order to make sure that both recordsets stay on same record - you can use
..Bookmark property:

rs2.bookmark=rs1.bookmark
--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com