Insertion Question (Access 2002, SQL, VBA)

[spacejones]

I am currently using as my database Access 2002. I have a table which
holds orders for certain products. Given that each record in this table
has a quantity ordered field. I want to populate another table called
inventory from this table. The problem I am seemingly facing is that I
don't know how to loop the INSERT command given a field quantity in
that table.

For example if I ordered 5 (Quantity Field) sofa's (Furiture Field) in
black (Color Field). This is in one table, I want to populate another
table with 5 records with say Furniture: Sofa and Color: Black.

For some reason I think this should be simple but maybe I am missing a
function or something. If you can give me some sql or vb using sql or
anything to get this done. Much appreciated.

-Chris

 

[Guest]

dim mysql as string
dim stFurniture as string
dim stColor as string

dim db as database
dim rs as recordset

set db = currentdb
set rs = db.createrecordset("SELECT quantity, furniture, color from
my_table", dbopendynaset)

rs.movefirst
do while not rs.eof

stFurniture = rs!furniture
stColor = rs!color

for i = 1 to rs!quantity
mysql = "INSERT INTO another_table (furniture, color) "
mysql = mysql & "SELECT stFurniture, stColor "
docmd.runsql mysql
next
rs.movenext
loop

set db = nothing
set rs = nothing

That's one way.

 

[spacejones]

Carol I love you. Thanks a lot.

-Chris

 

[spacejones]

Thank you, this is exactly what I need. One quick question, it is
saying method or member function not found. Do I need to add a lib to
make creatrecordset work. That is the one it is highlighting on the
error. thx, you're awesome.

Chris

 

[Guest]

No, you shouldn't need to add anything; this is really basic basic. I looked
carefully to see if I had made a typo, but I didn't see one -- that's the
usual reason for that particular message. If I see anything I will get right
back to you. Meanwhile, maybe you could check your spelling and field names
and so on?

 

[Guest]

I notice that you have said "creatrecordset" and not "createrecordset".
Could that be it?