writing contents of a collection to a new record

[Bob M]

Hi

I have a table which holds several records.
30 fields in each record are calculated every time I create a new record



In my code I hold these values in a collection

When I come to write a new record to the table is there a simply way of writing these 30 fields in one movement rather than specifying each of the 30 fields one at a time ?

Bob M

[orange]

Please tell us about the database--the table(s), relationships, forms, queries.....etc.

[June7]

A table that holds only 'several' records? Saving calculated data? 30 calculated fields? This sounds very odd.

What do you mean by 'collection'? Not sure I've ever used. I have used arrays.

Exactly how are you saving data? Are you opening a recordset object and setting value for each field of record? Or are you executing an INSERT action SQL?

Post your code.

[davegri]

In your collection, add the corresponding table field name to each element.
Then, using a loop, you can build an INSERT query from the collection to add the record.

[davegri]

Below demonstrates my suggestion in post #4 above.
Builds 2 element collection, then parses the two fields into the table fieldname and value portions of an Insert query.
The demo form shows each record as it is added to the table.

davegri-InsertFromCollection.zip

You could just as easily build 2 collections, one for the field names and one for the values, and build your query from those. That might be easier, as the field names won't change.
In that case, you wouldn't need fcnParse.

Second thought:
I believe that if you are very careful, you don't need to specify the table field names in an insert query. Just list the values in the exact right order to match the order of the field names in the table.
That way you would not need to explicitly name the fields in the query.
That said, all you need is your existing collection to build the query. However, this will only work if your table does not have an autonumber primary key.
Below is a example using a single element collection (just the values).

davegri-InsertFromSingleCollection.zip

[June7]

Now I know what a collection looks like.

Unless you want to reference fields by index or give them names like: Data1, Data2, Data3, etc., will have to type all 30 field names at least once somewhere. Whether to build collection, array, or a string.

[davegri]

Now I know what a collection looks like.

Arrays are so Yesterday, and Microsoft kindly gave us Collections.