Updating query criteria using VBA

[laniganb320]

We have 100+ customers that we are including in a report; we have to run a query for each customer, and they each have their own criteria that will be used.

I’m trying to create a macro that will:

• Update the criteria used in the query to Like "*Customer 1*"
• Run the query for the first customer
• Update the criteria to Like "*Customer 2*"
• Run the query again
• Update the criteria to Like "*Customer 3*"
• Etc.

What would the VBA look like to accomplish this?

 

[welshgasman]

I would do this.
In a table have the customer ID and possibly a boolean field to indicate customer should be included in the report run.
Create a recordset from that table where boolean field is true.
Loop through the recordset using the customer id for each record and build the criteria, then run the query.

HTH