Macro Loop

[elguapogm]

I currently have a form that asks for 2 fields to be filled with data to be passed to 3 separate macros that will then run a set of about 10-15 queries based on the values entered in the form. This used to be sufficient as the manually entered data was only a small set. Now the number of values that need to be run through the macros is starting to become too large to constantly be changing the values and running the macros. I can set up a table that will contain all the value combinations that would need to be entered into the form to then be passed to the queries, but how can I set this up to be done automatically. I know it can be set up using a loop, but I'm not familiar with VBA code (most of my access work is done using the design tools) so I'm not sure how to correctly set the fields that would be passed to my macros. I can also deal with the values being passed to my form one at a time, then have the macro run on that set of values, then have the next value sent to the form, etc . . .

To give a better idea as to what is being done, I have a table set up with data for all stores that I'm querying. I need to select the REPORT_BRAND and store_no from this table and run about 10-15 queries that will sort the data properly for a dedupe that will be performed at the end. The form that is set up asks for the REPORT_BRAND and store_no, then it gets passed to the macros which will create the tables for store #1. The values in the Form then get replaced with the next report_brand and store_no and the macros get run again for store #2. At the end of all the stores, I have 2 tables setup with all the final results for each store. These 2 tables then provide the results that I then manipulate to fill in the reports that are required. I need to run each store separately because the results as based on total record percentages by store.

I want to remove the manual intervention from the procedure and just have a table feed the form (or eliminate the form all together) and have the macros run for all stores on my table.

I've searched around and have seen a lot of examples of using a loop, but not being familiar with VBA makes understanding exactly what I need to enter for my specific data a bit difficult.

[rpeare]

if you are storing all of your variables in tables you don't need to populate a form at all but if you want to continue populating data in your form you can do this:

Let's say you have a table called 'tblStoredVariables

on that table your data looks like:

ID  Report_Brand  Store_No  Var1  Var2  Var3 ----> remainder of your variables

Further let's say you have a form called frmSetup with a combo box for both your REPORT_BRAND (cboReportBrand) and STORE_NO (cboStoreNo)
Now on your form you have fields called Variable1, Variable2, Variable3

after you've selected the store no you can do this:

dim db as database
dim rst as recordset
dim sSQL as string
dim iID as long

iID = dlookup("[ID]", "tblStoredVariables", "[Report_Brand] = " & forms!frmSetup!cboReportBrand & " AND [Store_No] = " & cbostoreno )

'(NOTE: This assumes your report brand and storeno identifiers are NUMBER VALUES, the dlookup changes a bit if they are text values)

set db = currentdb
ssql = "SELECT * FROM tblStoredVariables WHERE (ID] = " & iid & ")"
set rst = db.openrecordset(ssql)

'THERE SHOULD BE ONLY 1 RECORD FOR EACH REPORT_BRAND AND STORE_NO PAIR
if rst.recordcount <> 0 then
     rst.movefirst
     Variable1 = rst.fields("var1")
     Variable2 = rst.fields("var2")
     Variable3 = rst.fields("var3")
endif
rst.close
set db = nothing

if the form is correctly populating with your values you can then attach all your queries you want to run to the end of the code

you can of course cycle through ALL your stored variables the same way so that you could, theoretically, choose the report combinations you wanted to run from, say, a multi select list box and click one button and have it cycle through everything you want to run with a single click.

[elguapogm]

Thanks for the response. I'll take a stab at it later today and let you know the results.