Microsoft Access - Using Macro to pass parameters to a query multiples time based upon the input in a table

Question

I want to have a way to run a Macro that will open up a Table/form that has a list of products that I want to run a Query on. Once I get the table/form open, I want to pass to the query the current product number from the open table/form and have the query complete just for that one product number, and then I will export the finding using the MACRO also. Once that product information has been exported, I want to have the MACRO skip to the next record and do the same thing with all the records until we reach no more records in the open table/form.

Answer 1

You need a query based on the table which will give you all the product numbers. Opening the table or a form with a macro won't work for this.

If I understand correctly, you need to export the details for each product and to have each product's details export  separately.

You can create the query that will give the needed info about the product ready to export (first query).

Create another query that lists all the product numbers that you want to use, one at a time, in the first query above (this will be the second query).

You don't mention if you are comfortable with writing vba and why you specifically need a macro.  If you need help with the vba, post back.

You will need some vba code to open a recordset on the second query, grab the product number and feed it into the first query, then do your export.

With the recordset open on the second query, move to the next product number and repeat the process until you have exported info for each product.

---

Jeanette Cunningham MVP(Access) Pakenham, Victoria Australia