dlookup or find methods for fileds using balue for field name

[BobM]

i read the thread entitled "Convert Field Value String to Actual Field in Expression"

It appeared that the final solutions got moore complex not less complex

MY PROBLEM IS:
1. get a value from one table as a string
2. lookup the value of a field in a second table
where the field name = the value of field in table 1

I CAN GET THIS USING DLOOkUP, BUT NOT BY FIND METHOD
example:
c = value from tableR2 (if [PART] - "AN" then c = "HD1")





HERE IS MY CODE
Dim CX As Variant 'Specific HX DATE
Dim C As Variant
DIM SQL as String



Dim R1 as Recordset
' HX_DATES (PATID as Long), HD1, HD2 HD3 HD4 as Dates (double)
Dim R2 as Recordset
'HX_LIST ( PART as string) LIST OF HX VALUES
C = Null
CX = Null
SQL = "Select * from HX_LIST where [PART] = 'AN' ;"



Set R1 = DB.OPENRECORDSET("HX_DATES", Db_readonly)
SET R2 = DB.OPENRECORDSET (SQL)

PROBLEM:
Moving to first record of R2 gets value of "H01" (this is a string)
As example R2!H01 = #2/3/2006#
=========
THIS WORKS: CX = DLookup("" & C & "", "tmp_hex", "patid = " & PATID)

WHAT I WANT IS
R1.findfirst "PATID = " & PATID (This works)
C = "R!" & C (Makes C = R1!H01)
CX = R1!H01 (I want CX = #2/3/2006#)

[MMcCarthy]

Try this:

 
Dim CX As Variant 'Specific HX DATE
Dim C As Variant
Dim SQL As String
Dim db As DAO.Database
Dim R1 As DAO.Recordset ' HX_DATES (PATID as Long), HD1, HD2 HD3 HD4 as Dates (double)
Dim R2 As DAO.Recordset 'HX_LIST ( PART as string) LIST OF HX VALUES
    C = Null
    CX = Null
    SQL = "Select * from HX_LIST where [PART] = 'AN' ;"
    Set db = CurrentDb
    Set R1 = db.OpenRecordset("HX_DATES", DB_READONLY)
    Set R2 = db.OpenRecordset(SQL)
    rs2.MoveFirst
    C = rs2!HO1
 
    With R1
        .FindFirst "PATID = " & PATID
        CX = .Fields(C)
    End With
 
    R1.Close
    R2.Close
    Set R1 = Nothing
    Set R2 = Nothing
    Set db = Nothing