.AddNew

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Pockmire - 06 Mar 2005 12:55 GMT

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

Reply to this Message

Douglas J. Steele - 06 Mar 2005 13:14 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>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
[quoted text clipped - 7 lines]
> Col(100)=rs!Array(99)
> tdf.update

Reply to this Message

David C. Holley - 07 Mar 2005 01:06 GMT

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

> 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
[quoted text clipped - 7 lines]
> Col(100)=rs!Array(99)
> tdf.update

Reply to this Message

Jim Pockmire - 10 Mar 2005 00:09 GMT

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

> 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
[quoted text clipped - 13 lines]
>> Col(100)=rs!Array(99)
>> tdf.update

Reply to this Message