asked on

Further automation on reports that are automatically created in .SNP Format based on a table

I have some code behind a button that outputs a series of reports....my code is rather long because I just copied and pasted the same code over and over:

DoCmd.OpenReport stDocName, acViewNormal, , "[PartID] = 1234"
DoCmd.OpenReport stDocName, acViewNormal, , "[PartID] = 1235"
DoCmd.OpenReport stDocName, acViewNormal, , "[PartID] = 1236"
etc etc etc

What I would like to do is create a new table that only lists the partId's that I run reports on. How could I create the same code to output the same reports by reading the first record in the table and using that value to ouput the report with PartID = value in record 1...then going to record 2...etc etc.

I know it is probably easy, but im stuck.

SOLUTION

AngelinoM

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peter57r

Hello robbdfw,

Base you report on a query that selects based on matches in the current source and the (new) parts list table.

Add the current source table/query and the new table to a new query and join them on partid save as a new query (q1 or whatever)
Change the report's recordsource to the new query by displaying the report's properties and selecting the new query (q1).

Pete

ASKER CERTIFIED SOLUTION

SidFishes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SidFishes

obviously criteria like discontinuedyn could be changed to whatever is appropriate

SidFishes

oh...and you'd need a label called Status for the progress indicator (if you want it )

the benefit to this approach is that you can do any range of reports without manually creating each line docmd.openreport like you are currently doing...

SidFishes

err...and i should point out that this code outputs to snp format ...you can change the OutputTo
to any of the following

acFormatASP
acFormatDAP
acFormatHTML
acFormatIIS
acFormatRTF
acFormatSNP
acFormatTXT
acFormatXLS

or change outputTo to PrintOut if you want multiple copies

DoCmd.SelectObject acReport, "RPTCaseLAbels", True
DoCmd.PrintOut ,,,2

or just use

Docmd.openreport "MyReport", acViewNormal

R Davignon

ASKER

Sid...That code rocked... thanks for all the info....some points to you too Angelino