How to use one field value to identify other field name

[Marty2of5]

Hey there
I'm trying to compare data between recordsets. What I want is take the value of one field in rst1 (rst1!flag = A) and look up the value in rst2 in the field of that name (rst2!A = ?). What is the syntax for doing that?

Dim fldname

Set fldname = rst1!flag

If rst1!subflag = rst2!"fldname" Then
rst1.Move Next
Else
'Update the record with the new value in rst2
rst1.MoveNext
End If

Of course, this doesn't work. But how do I code it so that it looks up rst2!A if rst1!flag = A?

Thanks for your help.

Marty

 

[DougP]

for Access 2000 and newer
need to checkmark "Microsoft ADO 2.5 ..." or better in "Tools" menu "References" in VBA editor

Dim Rst1 As ADODB.Recordset
Dim SQLCode, SQLCode2 As String
Set Rst1 = New ADODB.Recordset
Set rst2 = New ADODB.Recordset

SQLCode = "Select * From yourTable Where [flag] = 'A';"
Rst1.Open SQLCode, CurrentProject.Connection

SQLCode2 = "Select * From yourSecondTable Where [flag] = '" & Rs1!flag & "';"
rst2.Open SQLCode, CurrentProject.Connection

Debug.Print rst2!somefield ' < this returns a value from the second table based on...
'what was found in the first table

Set Rst1 = Nothing
Set rst2 = Nothing
Set Conn2 = Nothing

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Marty2of5]

Thanks Doug

But it looks like your SQL criteria are still matching one field value to another field value. I'm trying to get a field value to match to field name. Also, the database I'm working on is in Access 97. I want to loop through the entire recordset, not just 'A'.

rst1 contains the fields ID, Flag and Subflag
rst2 contains the fields ID, A, B, C, D, E ...

The values in rst1!Flag can be A - Z.
The values in rst1!Subflag can be 1 - 50.
The values in rst2![A...Z] can be 1 - 50.

I want to move through rst1, find the record in rst2 with the matching ID, and compare the value of rst1!Subflag with the value of rst2!(letter) where the (letter) equals whatever is in rst1!Flag.

I hope I have explained myself better this time.

Thanks a lot, Marty