Solved

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

Posted on 2004-10-20
7
248 Views
Last Modified: 2008-03-06
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.
0
Comment
Question by:robbdfw
7 Comments
 
LVL 2

Assisted Solution

by:AngelinoM
AngelinoM earned 50 total points
ID: 12359197
Create a recordset to query your table and then put
Recordsetname.movefirst
While not Recordsetname.EOF
   DoCmd.OpenReport stDocName, acViewNormal, , "[PartID] = " & RecordsetName!FieldName
   Recordsetname.movenext
wend
0
 
LVL 77

Expert Comment

by:peter57r
ID: 12359200
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
0
 
LVL 36

Accepted Solution

by:
SidFishes earned 250 total points
ID: 12359807
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
      .MoveFirst
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
                doEvents
      .MoveNext
Wend
.Close
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





0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 36

Expert Comment

by:SidFishes
ID: 12359819
obviously criteria like discontinuedyn could be changed to whatever is appropriate
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12359959
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...
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12360142
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

0
 

Author Comment

by:robbdfw
ID: 12363813
Sid...That code rocked... thanks for all the info....some points to you too Angelino

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now