Repeat MACRO and Report using Code

[JohnS]

I have a macro that runs a series of queries and creates a Report. The
queries pull data from a table that contains a list of states. What I want
to do is run the macro and create the report once for each state in the
table. How do I make the macro and report repeat as many times as I have
unique state values? For example, if the table contains the states, IL, CA,
and NY, I want the macro to run three times, and produce three reports.

Thank You

 

[Ofer]

If the state table contain one record for each state then you can use that

Dim I as Integer
For I = 1 To Dcount("*","[Table Name]")
docmd.RunMacro "MacroName"
docmd.OpenReport "ReportName"
Next I

I wasn't sure if you want to use each state in the loop, if so , you need to
open a recordset and create a loop, if that the case please give me some more
details on what you need, how do you want to use this parameters.

 

[Ofer]

Create a query that group by the state,

SELECT TableName.State
FROM TableName
GROUP BY TableName.State

Then you can count the query records
For I = 1 To Dcount("*","[Query Name]")
===============================
Or
Open a recordset

Dim MyDB as Dao.DataBase, MyRec as Dao.Recordset , I as Integer
Set MyDB = currentdb
Set MyRec = MyDB.OpenRecordset("SELECT TableName.State FROM TableName GROUP
BY TableName.State")
MyRec.MoveLast

For I=1 To MyRec.RecordCount


Next I


--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

Sorry for not being clear. The state table can contain multiple entries of
the same state.

If the state table contain one record for each state then you can use that

Dim I as Integer
For I = 1 To Dcount("*","[Table Name]")
docmd.RunMacro "MacroName"
docmd.OpenReport "ReportName"
Next I

I wasn't sure if you want to use each state in the loop, if so , you need to
open a recordset and create a loop, if that the case please give me some more
details on what you need, how do you want to use this parameters.

--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

I have a macro that runs a series of queries and creates a Report. The
queries pull data from a table that contains a list of states. What I want
to do is run the macro and create the report once for each state in the
table. How do I make the macro and report repeat as many times as I have
unique state values? For example, if the table contains the states, IL, CA,
and NY, I want the macro to run three times, and produce three reports.

Thank You