How to run mutiple reports based on variable in vba

[msyth2]

Could anyone help me on the following scenario:

I have a TABLE_A containg fileds such as Division, Year, Month, Amount

How do I set up a macro that can exort a few reports seperately based on divison variable?

I am thinking about a structure like this:

-enter non-loop variable - Year, Month

-Loop on Divison
-SQL based on the variables
-report based on the sql (export to file) (pre-setuped report template)
-end loop


Unfurturnately I only know a little simple stuff about vba... Can anyone suggest some key functions/structures?

Thank you very much.

 

[MarkK]

Hey there welcome to the forum.
I suggest you read up on the DAO.Recordset object. A recordset represents an in-memory cache of data from a table so if a table is like a warehouse, a recordset it a bit like a forklift. And I expect you'll need a recordset to control the loop you are talking about. You'd open a recordset based on the Divisions you want to report on, step through each record and do some unit of work.

dim rst as dao.recordset
set rst = currentdb.openrecordset( _
  "SELECT Month, Year FROM Table WHERE Division < 5;")
with rst
  do while not .eof
    docmd.openreport "SomeReport", OpenArgs:= Month & " " & Year
    .movenext
  loop
  .close
end with

HTH

 

[msyth2]

Thank you so much for reply. If Im understanding currently recordset is cutting a portion of a table for use?

And how do I associate a report to this macro? I created the report based on a hardcoded sql, otherwise i dont know how to create those fields.

 

[MarkK]

To open a report use 'DoCmd.OpenReport.' You can hardcode the SQL in the report and then filter that SQL using the 'FilterName' or 'WhereCondition' arguments of the OpenReport action.

 

[msyth2]

What if I need to export them to file? if I use outputto, there is no filter arguement.

thanks.

Sorry for shooting misellenous questions one at a time, but how do i set up a set of number, then loop while using the number in their as variable?

can I do things like
set i in (1,2,3,4,5)
for each i ...

or i need recordset?

 

[msyth2]

bump to look for help