Hi

I have 2 recordsets rs1=ADO recordset and rs2=DAO recordset.
I need to filter rs2 inside a loop as rs1 changes value for each loop and I need to find the same value in rs2.
Anyone know how to acheive this?

Cheers / Fred

Yeow!

Here is how I did a similar thing with two recordsets, one in Access, and one in SQL. Basically you open both recordsets and then manage your loops.

'==========
On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

strDate = Date
strTime = Time

strUserName = "SQLUserName"
strPassword = "SQLPassword"

'Access DSN
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "DSN=AccessDSN;"
objRS.CursorLocation = 3

'SQL Server DSN
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=SQLDSN;", strUserName, strPassword
objRecordset.CursorLocation = adUseClient

'Get Access Data or rs1
objRS.Open "SELECT * FROM qryAccessQuery", objConn, 3, 3

'Get SQL Data or rs2
objRecordset.Open "SELECT * FROM tblSQLTable" , objConnection, adOpenStatic, adLockOptimistic

Do While Not objRS.EOF 'Loop through Access recordset
    objRecordset.AddNew 'Add to SQL table
        For Each fld In objRS.Fields
        objRecordset(fld.Name) = fld.Value
        Next
        objRecordset("DateField") = strDate
        objRecordset("TimeField") = strTime
        objRS.MoveNext
Loop
        objRecordset.Update

'Close Access
objRS.Close
objConn.Close

'Close SQL
objRecordset.Close
objConnection.Close
'==========

You can do your own looping based on the fields you are checking and what you are checking for. If I had more info I could get more specific.

This is written in VBScript, btw, so will work with VBA perhaps with slight modifications.

I hope this gets you started anyway.



mmcdonal