Can you use a variable for the Field name when using AddNew to a record set?

ms-access vba ms-access-2010 recordset
Sinister Beard picture Sinister Beard · Feb 19, 2014 · Viewed 20.4k times · Source

I'm using a DAO recordset to update a table because of problems enocuntered here.

This works fine when I know the name of the field I'm updating, e.g.:

rs2.AddNew
rs2![ContactID] = rs.Fields(0).Value
rs2![Fee Protection Insurance] = "" & strValue & ""
rs2.Update

works perfectly.

However, the field that I'm trying to update won't always have the same name, so I attempted to use a variable here too, expecting it to evaluate and be equivilent to the above code:

rs2.AddNew
rs2![ContactID] = rs.Fields(0).Value
rs2!["strFieldName"] = "" & strValue & ""
rs2.Update

but it tells me that item's not in the collection, even when strFieldName is set to Fee Protection Insurance.

I've tried this various ways, including:

rs2![" & strFieldName & "] = "" & strValue & ""

rs2![strFieldName] = "" & strValue & ""

rs2!["" & strFieldName & ""] = "" & strValue & ""

rs2![cStr(strFieldName)] = "" & strValue & ""

none of which work.

Am I going about this the wrong way, or am I attempting something impossible?

Answer

Dmitry Pavliv picture Dmitry Pavliv · Feb 19, 2014

Try to use this one:

rs2.Fields(strFieldName) = "" & strValue & ""

Related questions

how to read one field from one record

I know I'm over thinking this, but I want to check a single value/field within a single record. For instance, I want to know if the value of the "closedDate" field in the record with the primary key of 33 …

Read More
How to insert values into the database table using VBA in MS access

I've started to use access recently. I am trying to insert a few rows into the database; however, I am stuck as it is throwing an error: Too few parameters. I have a table test with only one column in …

Read More
Access 2010 VBA query a table and iterate through results

I have a query that I want to execute against a table. With the results I want to do something. In my head the pseudo code is: var q = "select * from table where some condition"; var results = db.getResults(q); …

Read More