rst field name from string

[supmktg]

I am looping through a recordset and updating another table based on a field in the current record in the loop. The field names in this recordset change depending on the source of the update file. On my form I have a combo from which the user can select the correct field name. I need to be able to specify rst! & FieldNameSelectedFromCombo in my sql.

Here's my code so far:

Dim rst as DAO.Recordset
Dim sql as string, strFldName as string

strFldName = Me.Combo1.value

Set rst = CurrentDb.OpenRecordset("UpdateRecs_tmp")

sql = "Update Table1 Set Amount = " & rst!strFldName & " Where ID = " & rst!ID

Do Until rst.EOF

Currentdb.Execute (sql)

rst.MoveNext
Loop

How do I build rst! & FieldNameSelectedFromCombo so I can use it in my sql?

Thanks,
Sup

 

[boblarson]

sql = "Update Table1 Set Amount = " & rst(strFldName) & " Where ID = " & rst!ID

 

[supmktg]

Perfect!

Much appreciated,
Sup