.AddNew

[Jim Pockmire]

I am using .addnew to add 100 columns per record to a table from an array.
Is there a way to loop through the code so as not to have to write 100
individual assign statements as below?

tdf.addnew
Col1=rs!Array(0)
Col2=rs!Array(1)
...
...
Col(100)=rs!Array(99)
tdf.update

 

[Douglas J. Steele]

Might I suggest that if you've got 100 fields in your table like that, your
table probably hasn't been properly normalized?

To answer your specific question, though, try:

tdf.AddNew
For intLoop = 0 To 99
tdf.Fields("Col" & intLoop + 1) = rs!Array(intLoop)
Next intLoop

And, for what it's worth, you shouldn't be using Array to name anything:
that's a reserved word.

 

[David C. Holley]

Is there any particular reason why you're working with 100 columns?
While there it is possible to do what you ask, I'm curious about the
bigger picture.

David H

 

[Jim Pockmire]

Perhaps I am going about this incorrectly. My challenge seems well suited to
a pivot table, but I am not sure a pivot table will handle it. Essentially,
I have a table of sales data, with additional columns for cityID, storeID,
and customerID. I want to create a report for each cityID that has the
storeID as the columns, customerID as the rows, and sum of sales as the cell
value. The number of stores and customers varies by city, and the number of
stores in a city can be as high as 100.

I need a way to create a query or table of cityID header names (for each
city) as well as load the data into the apropriate columns in a query.

I won't try to explain the method to my madness. What do you suggest?

Jim