use a variable as fieldname in recordset

What I want to do is use a variable as the name for a field in a recordset. I've got a recordset called [export]. This recordset has 50 fields called [field1], [field2], , , [field50]. I use a variable called Counter. I don't know which data to put in which field before I start filling the table so I use the counter variable to place the data in a specific field.

Syntax I'm trying to use is lliek this:
dim counter
Set [ex] = dbs.openrecordset("export")
ex.addnew
if .... then
 ex!field & counter = "test"
 counter = counter + 1
end if
if .... then
 ex!field & counter = "test2"
 counter = counter + 1
end if
etc...

However this doen't work, does anyone know a sollution for this?
SykeyAsked:
Who is Participating?
 
kulikuliConnect With a Mentor Commented:
example:
myrecordset(field & cstr(counter))="test2"
0
 
kulikuliCommented:
using your recordset named ex it would be like:
ex(field & cstr(counter))="test2"
or to explain a little more another example:
set rs=db.openrecordset("MyTable",dbopendynaset)
   rs.addnew
   for intCount=0 to rs.fields.count-1
      strFieldname="Field" & cstr(intCount)
      rs(strFieldname)="NewValue"
   next intCount
   rs.update

'the above procedure fills all fields in the new record with the value "NewValue"

Once again another example that is fieldname-independend:
rs.addnew
for intCount=0 to rs.fields.count-1
   rs.fields(intCount)="MyValue" 'All fields get filled with this value
next intCount
rs.update

Hope this helps,

kulikuli
0
 
PoddyCommented:
i think you should consider normalising your data, too
0
 
kulikuliCommented:
Poddy is right. It isn't a very good idea to have a table with fieldnames like you describe. Try normalizing your datamodel and apply the last example I provided.
0
 
SykeyAuthor Commented:
I agree about normalising my data, but this is for a temp-table and I don't know what data will be put in which field. This depends on the wishes of the user and can change every time..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.