How can I equate 2 record sets?

[Newtester]

Hi,

Here is how I am connecting to access database:
rs As ADODB.Recordset
rs1 As ADODB.Recordset

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & MyAppPath & "\Vehicle_sales.mdb"

cn.Open
Set rs = New ADODB.Recordset

sSql = "SELECT * FROM Payments "
sSql = sSql & "WHERE PO_id = " & POid & " "
sSql = sSql & "AND Cust_id = " & Cid

Set rs = cn.Execute(sSql)

No I want to equate rs1 = rs. How can I do that?

Thanks in advance.

[cyberpd]

you are trying to equate two recordsets.
that means you want the same result-set in the two recordset..
you have already done this..

Set rs = cn.Execute(sSql)

you can repeate the same with different recordset..

Set rs1 = cn.Execute(sSql)

[amrita]

Check this for comparing two ADODB recordset

 Do While rs1.EOF = False
        Do While rs2.EOF = False
            If Trim(rs1("Value")) <> Trim(rs2("Value")) Then

            Else
                rs2.MoveNext
                Exit Do
            End If
        Loop
        rs1.MoveNext
    Loop

[Newtester]

Thanks for a quick reply guys.

Here is what I am trying to do. My rs record set is defined as private in my form. I am reading a record in rs record set in one procedure then after assigning few fields out the rs record set to the text boxes I am calling another procedure where rs1 record set is defined as a local for this procedure and I HAVE TO read a different table but in the same rs record set so before I do that I want to save all the values of rs record set into the rs1 record set and at the end of this procedure put back all the values into rs record set from the rs1 record set.

How can I achieve this?

[ganeshmoorthy]

Im confused...what do you want to do actually...

[amrita]

Loop through rs recordeset to put/save values in rs1 defined on same table.

[Newtester]

Amrta,

Can you please give me an example of how to loop thru to save values? I am getting errors the way I am trying to loop thru.

Thanks in advance.

[amrita]

What you have tried just show me ??

[Al42]

Look in the ADO help for the Clone method. There's an example too.

[Newtester]

Amrita,

I tried the following 2 ways which did not work for me.

1. rs1 = rs

2. For I = LBound(rs.fields) to UBound(rs.fields)
rs1.fields(I) = rs.fields(I)
Next I

Please tell me how should I be doing this?

Thanks.

[amrita]

Check this (just an example)!

Dim rs As New ADODB.Recordset
Dim rs1 As ADODB.Recordset

        Set rs = conn.Execute("Select EmpId From tblEmp")       
        Set rs1 = New Recordset
         rs1.Fields.Append "EmpId", adInteger
         rs1.Open
        Do While rs.EOF() = False
                rs1.AddNew
                rs1.Fields("EmpId").Value = rs("EmpId")
                rs1.Update
                
               rs.MoveNext
        Loop

[NEOMEGAX]

If I understand you . It is Easy


I needed to take the same values in two Recordsets of Diferent kind of declaration (Public, Private) and i use it :

Public Function buscaTesisRel(strCveGen As String, strTipoDoctoOrigen As String) As Recordset

Set buscaVotoRel = recRet


Set recGlobalRelacion = buscaTesisRel(strSentenciaSeleccionada, GstrTipoDocto)

'FIRST YOU NEED TO SET THE RECORDSET TO NOTHING 

Set recGlobalRelacion = Nothing

In your case do :

Set rs1= Nothing
Set rs1=rs

Warning: I used this kind of Recordset declaration:

Dim recRet As New Recordset

I think that ADODB.Recodset is the same


Bye

[leinad31]

Thanks for a quick reply guys.

Here is what I am trying to do. My rs record set is defined as private in my form. I am reading a record in rs record set in one procedure then after assigning few fields out the rs record set to the text boxes I am calling another procedure where rs1 record set is defined as a local for this procedure and I HAVE TO read a different table but in the same rs record set so before I do that I want to save all the values of rs record set into the rs1 record set and at the end of this procedure put back all the values into rs record set from the rs1 record set.

How can I achieve this?

Rather than going through all the trouble of passing data back and forth between the recordsets, why not just have each communicate directly with the database (leaving them unaware of each other) and have each recordset requery the database so their data is up to date....

Why not a straightforward implementation? Let me guess... your using data bound controls aren't you?

[Hack]

Amrta,

Can you please give me an example of how to loop thru to save values? I am getting errors the way I am trying to loop thru.

What are the errors?

Warning: I used this kind of Recordset declaration:

Dim recRet As New Recordset

It should be:

Dim recRet As ADODB.Recordset 'to initialize
'then
Set recRet = New ADODB.Recordset 'to actually use it