Referring to recordset fields using programmatically assigned ordi

[Guest]

After I assign non-contiguous ordinal positions to tabledef fields, can I use
those positions to refer to the fields in the related dataset?

I have a module that is tantamount to an update query with extensive data
integrity manipulation.

I access a record in tbl1, move several fields to an array, perform checks
and reset values in the array, and transfer the array to several fields in
tbl2. For those fields, I have set the ordinal position to 100 to 106...

tdf2!fld1.ordinalposition=100 ...

What I'd like to do is...

for i=0 to 6
rst2.fields(100+i)=arrayDate(i)
next

....but I receive an error message "item not found in this collection."

The array is essential to the integrity-checking routine, and using 100+i
seemed to be an intuitive way to populate the output record. (I have several
other fields in the tbl2 record)

Any wisdom is appreciated.

 

[Douglas J. Steele]

Do you have 106 fields in your table, or are you hoping that you'll fool
Access into thinking you do by using the OrdinalPosition?

If you've got 106 fields in your table, then I suspect it's in dire need of
normalization.

 

[Guest]

I have about a dozen fields. I was hoping to use 100+i to clearly identify
the correlation between the array items and the table fields.

 

[Douglas J. Steele]

OrdinalPosition is the actual position: you can't fool it like that.

You'll either have to use named fields, or else have a cross-reference so
that you know which field number it is with which you want to interact.

 

[Guest]

Perhaps you might consider creating an array (or a
collection) of field objects:

Dim a(100 To 116) As DAO.Field
Dim rs As DAO.Recordset
Dim db As DAO.Database

....

For i = 0 To rs.Fields.Count - 1
Set a(100 + i) = rs.Fields(i)
Next i

Instead of using a code loop, you could set any element
of the arrary (or member of the collection) to be any
field.

(david)