(Access 2002) How to create numerous reports from another list

[MrRivethead]

Hi all... Hopefully I can make this question make sense.

* I've created a report that displays 4 charts on one page (a four-square chart).
* Each individual chart pulls data based off of one similar criteria (a company code number).
* Access then pushes the four square chart to a server location (as a SNAP file).
* The result is a four-square chart for one individual company name sitting on a server.

My problem: I have approx. 800 companies I need to do this to.

Is there a way to have access look at a list (table) of company names, input them into the appropriate queries that make up my chart, and finally push that chart to a server somewhere with a unique file name based off that company?

I found this in the Access Help screen:
Manipulate records one at a time. You can use Visual Basic to step through a set of records one record at a time and perform an operation on each record. In contrast, macros work with entire sets of records at one time.

...hmmm.... OK, so where do I get to do this?

Obviously automation for this is needed.... Any and all help will be greatly appreciated! Thanks!!!

 

[dhookom]

I assume your report has a record source that can be filtered to a single Company and that you have some code to export the report as a snapshot to a particular location on your server. To loop through all companies, your code might look like:
[blue]

Dim lngCompanyID as Long  'primary key value from tblCompanies
Dim db As DAO.Database
Dim rs As DAO.RecordSet
Dim strSQL as String
Set db = CurrentDb
strSQL = "SELECT CompanyID, CompanyName FROM tblCompanies"
Set rs = db.OpenRecordset(strSQL)
With rs
    Do Until .EOF
        'code to export to file
        .MoveNext
    Loop
    .Close
End With
Set rs = Nothing
Set db = Nothing

[/blue]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.