AddNew and Cycling through Table Fields

  • Thread starter Thread starter BK
  • Start date Start date
B

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
 
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
 
Back
Top