Update Record in one Table from Another Table

[MikeKohler]

I have the following code, what I want to do is if one record has a certain value then the record in the second table will now contain that value. This is the code that I have been using.
Sub CombineFields()

Dim db As Database
Dim rst1 As Recordset 'PT001
Dim rst2 As Recordset 'tbl01
Dim tbl1 As TableDef 'PT001
Dim tbl2 As TableDef 'tbl01
Dim fld As Field

Set db = CurrentDb()
Set rst1 = db.OpenRecordset("PT001&quot
Set rst2 = db.OpenRecordset("tbl01&quot

For Each fld In rst1.Fields

If "ROLL" = "dROLLNUMBER" Then

rst1.Edit
rst1![Contact] = rst2![CUSTNMBR1]
rst1.Update
End If
Next
rst1.Close
rst2.Close
End Sub

Thank you,
Michael Kohler
mkohler@telusplanet.net

 

[Katerine]

Hi Mike,
I'm a little unclear about what specifically you need to update, but I think I see the problem (I assume the problem is that the code runs and nothing happens?).

If "ROLL" = "dROLLNUMBER" Then

Since both "ROLL" and "dROLLNUMBER" are enclosed in quotes, it's most likely evaluating both as strings. "ROLL" is never equal to "dROLLNUMBER" -- "ROLL" is only equal to "ROLL".

Anyway, that appears to be the problem.. not sure about the solution, since I'm not sure what you want

Hope that helps!