Refer to Field in Recordset via String 3

[TimTDP]

In Access 2000 I need to add a new record to a recordset. However I need to build the field name via code, so my code looks like this:

Dim txtField as string
Dim Kount as integer

Do Until .EOF
txtField = "Container" & Kount & "Id"

rstQuotationProductContainer
.AddNew
!ProductId = intProductId
!txtField = intContainerId
.Update

Access does not like the txtField in !txtField = intContainerId

What should the syntax be?

 

[JerryKlmns]

TimTDP,

Does this field already exist in your table, before you put a value there? And if so, your table structure is something like

...
...
Container01Id
Container02Id
Container03Id
Container04Id
Container05Id
...
Container99Id
...

You should reconcider that structure to something like

...
...
Container <---- This shall define the value
Id <---- This shall define which Container has that value
...

 

[Remou]

I agree with JerryKlmns. Here are a few notes on why Jerry is right.
Relational links:

http://www.alphabetcitydataworks.com/fundamentals.htm

http://r937.com/relational.html

(same as above, different format)
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

However, if you must do this, try:
.Fields(txtField)
With the warning that the longer you work with this set-up the worse the problems are likely to get. You may end up with a situation where you will lose all the time you have invested.

 

[PHV]

Anyway, the correct syntax was:
rstQuotationProductContainer.Fields(txtField) = intContainerId

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886