AddNew and Cycling through Table Fields

[BK]

I was trying to add records programmatically by looping through a
recordset. I also wanted to clean it up so that I don't have to hard
code in the field names either and I can't seem to get this to work. As
a note I do have "rs" defined and opened in code above it so rs and rs3
are intentionally different.

This works just fine....

Set rs3 = db.OpenRecordset("tblEWOs")
With rs3
.AddNew
!Contract_Number = rs("Contract_Number")
!Job_Number = rs("Job_Number")
!Job_Type = rs("Job_Type")
etc...
.Update
End With

What I'd like to be able to do is something like this....

Set rs3 = db.OpenRecordset("tblEWOs")
With rs3
.AddNew
For x = 0 To .Fields.Count - 1
strField = .Fields(x).Name
!strField = rs(strField)
Next x
.Update
End With

But the second example errors out because it can't find a field in the
recordset called "strField", which is obviously not there because I want
it to look for the value of strField not strField itself. Anyone know
how I might be able to do this? This particular table has about 50
fields. And although I've already typed it all out, I'd like to make it
a tad more dynamic and not have to change my code should the fields
change. And avoid all that typing in the future along with the
associated typos.

TIA,
Bill

 

[Albert D. Kallal]

You can always use (assuming you don't use the "with":

rs3("Contract_Number") = rs("Contract_Number")
rs3("Job_Number") = rs("Job_Number")

so,

rs3.AddNew
For x = 0 To .Fields.Count - 1
rs3(x) = rs(x)
next x
..Update