Variable field name in VB ?

[shar]

I have a table with fields Item01, Item02,..., Item35<br>The code creates a dynamic array fld(j).<br>I need to have a loop that refers to 'Item##' to copy the array in it, i.e.:<br>&nbsp;&nbsp;&nbsp;&nbsp;For I = 0 to k<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Item(I)] = fld(I)<br>&nbsp;&nbsp;&nbsp;&nbsp;Next I<br>I do not know how to write the expression for the field name to increment and have VB recognize it as a field name.<br><br>Any help is appreciated.<br>Thanks.<br>Shar<br><br><br>

 

[Chopper]

shar,<br><br>You could use for...each instead of the for...next loop.&nbsp;&nbsp;<br>It looks something like this:<br><br>*****<br><br>dim db as dao.database, tbl as dao.tabledef, fld as dao.field<br>dim strFieldName as string<br>set db = currentdb<br>set tbl = db.tabledefs![yourtablename]<br><br>'Iterate over the fields in the table<br>for each fld in tbl.fields<br>&nbsp;&nbsp;&nbsp;&nbsp;'Assign the field name to your variable<br>&nbsp;&nbsp;&nbsp;&nbsp;strFieldName = fld.name<br>&nbsp;&nbsp;&nbsp;&nbsp;... The field name is available for you to manipulate<br><br>next<br><br>set tbl = nothing<br>set db = nothing<br><br>*****<br><br>Ideally, this will help, it allows you to iterate over the field names.&nbsp;&nbsp;Of course, you can increment a variable within the for...each loop to move your array along.&nbsp;&nbsp;It was hard for me to determine if you wanted to store the field names or the values in fields.<br><br>-Chopper<br>

 

[shar]

Chopper,<br><br>Thanks for the response.&nbsp;&nbsp;My problem is when I have the field name in a variable, how I can assign a value from the array to it.&nbsp;&nbsp;Here is the statement in the code:<br><br>&nbsp;&nbsp;&nbsp;'fldvar is a variable with the field name stored in it.<br><br>&nbsp;&nbsp;&nbsp;rs![fldvar] = fld(J)<br><br>However, I get an &quot;Item not found in collection&quot; error.<br><br>I have tried:<br>&nbsp;&nbsp;&nbsp;&nbsp;rs![&quot; & fldvar & &quot;] = fld(J)<br>&nbsp;&nbsp;&nbsp;&nbsp;rs![' & fldvar & '] = fld(J)<br>&nbsp;&nbsp;&nbsp;&nbsp;rs!&quot;[&quot; & fldvar & &quot;]&quot; = fld(J)<br>&nbsp;&nbsp;&nbsp;&nbsp;rs![&quot;fldvar&quot;] = fld(J)<br><br>I keep getting some kind of error msg. <br><br>Any thought on this?<br>Thanks.<br>Shar<br><br><br>