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.
Who is Participating?
SidFishesConnect With a Mentor Commented:
if you place this code behind a button, it will loop thru all products in your table and output snp's

Private Sub cmdCompile_Click()

Dim strSQL
Dim maxCount  As Long
Dim currCount, currDone As Integer
Dim currdonePct As Integer
Dim x

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
strSQL = "SELECT * from [tblProducts] where discontinuedyn = false and productidupc between 1 and 99999"
Set rst = dbs.OpenRecordset(strSQL)
With rst
      maxCount = .RecordCount
      currCount = .RecordCount
While Not .EOF
      pubProductIDupc = !ProductIDupc
      DoCmd.SelectObject acReport, "RPTCaseLAbels", True
      DoCmd.OutputTo acOutputReport, "RPTCaseLabels", "Snapshot Format", "\\server\snps\case\" & pubProductID & ".snp"
      currCount = currCount - 1
      currDone = maxCount - currCount
      x = currDone / maxCount * 2 'for us and cdn
      currdonePct = CInt(x * 100)
      me!Status.caption = currdonePct
MsgBox "Labels Compiled"
End With
Set rst = Nothing
Set dbs = Nothing
End Sub

to specify a range just change the sql and add 2 text boxes to your for startRNG and EndRNG

strSQL = "SELECT * from [tbl products] where discontinuedyn = false and productidupc between " & me!startRNG & "and" & Me!EndRNG

AngelinoMConnect With a Mentor Commented:
Create a recordset to query your table and then put
While not Recordsetname.EOF
   DoCmd.OpenReport stDocName, acViewNormal, , "[PartID] = " & RecordsetName!FieldName
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).

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

obviously criteria like discontinuedyn could be changed to whatever is appropriate
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...
err...and i should point out that this code outputs to snp format ...you can change the OutputTo
to any of the following


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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.