make table with vba adding records times quantity column

P

piersonpro

I need to make a table from invoice table copying each record the number of
times listed in the quantity field. this table is for the purpose of printing
labels, hence the need for duplicating record by quantity field.
 
M

Marshall Barton

piersonpro said:
I need to make a table from invoice table copying each record the number of
times listed in the quantity field. this table is for the purpose of printing
labels, hence the need for duplicating record by quantity field.
Click to expand...


Don't use a table for this kind of thing. You can do it
with a query, but you will need another table to help with
the process.

First, create a new table named Numbers with one field named
Num. Then populate the table with the values 1,2,3,... up
to more than the greatest number you will ever have in the
quantity field.

With that taken care of, you can use a query like:

SELECT Num, itemdescr, quantity
FROM invoicedata, Numbers
WHERE Num <= quantity

Use that query as your label report's record source. Note
that you can use the Num and quantity fields in a report
text box to print N of M on the labels.
 
M

Marshall Barton

piersonpro said:
Thank you for your response. however, I need to make a table that will be
saved in another database for my label program. The label program polls for
this table. When it finds the table it prints all records (labels). Once my
label program makes the labels (one per record), the program deletes the
table. This is why I need a record for each invoice items quantity.

I wanted to write it in vba so I can have a button on the invoice form
marked 'make labels'. This would make a table as described above.
Click to expand...


Sounds a little clumsy to me, but it's not my program.
Regardless, you can use the above query in a make table
query and run it from your code. The make table query can
even make the table in the other database without first
making the table in your database.

Assuming my query above is named qryinvoicelines (and that
you modify it to select a specific invoice), the code to
make the table in your database would be like:

strSQL = "SELECT Num, itemdescr, quantity " _
& "INTO newtable " _
& "FROM qryinvoicelines"
CurrrentDb. Execute strSQL, dbFailOnError

To make the table in the other database:

strSQL = "SELECT Num, itemdescr, quantity " _
& "INTO newtable IN ""path to other db"" " _
& "FROM qryinvoicelines"
CurrrentDb. Execute strSQL, dbFailOnError
 
Top