Solved

Printing 'x' number of copies for each record returned from Query. VBA macro.

Posted on 2004-09-20
18
902 Views
Last Modified: 2012-06-21
Hey Experts...

    This topic was covered in another thread, but I was hoping to get more insight, as I am having some real trouble with this.  I originally thought this task would be cake, but I'm stumped.  I thought perhaps I should post a new question, since my parameters are different from the previous question (I'm running the routine against a query dynaset, as opposed to a table).

I have a database with one table (tblDistList), one form (frmDistList), a query (qrySTP), and a report (rptCoverPage) which populates a cover page based on records from the query.  There are over 1550 records in tblDistList, and the query returns dynasets based on the Report# (STP is the field name).  We need to to be able to run the rptCoverPage report, enter the STP number for the query, and print all the resulting records.  Each record in the report needs to be printed multiple times, based on a variable available in the record (QTY is the field name).

I've tried attaching a nested loop to a button, but ran into a problem when I tried to use the OpenRecordSet function.  (I still don't know what that problem was, but I've since rebuilt the database, and OpenRecordSet seems to work again.  Check out this question for more details http://www.experts-exchange.com/Databases/MS_Access/Q_21137779.html)

I discovered EdithF's solution on the following thread (http://www.experts-exchange.com/Databases/MS_Access/Q_10057464.html), and that seemed like exactly the solution I needed.  It applied the an array with a loop to the Open Report event.    However, I'm now getting parameter errors from OpenRecordSet, and array errors from the line:  " intNumberRepeats(i) = Forms!Distribution!QTY(i)"

Here is my code.  Can anyone help?  I'm sufficient at VBA in Excel, Powerpoint, and Word, but I'm beginning to get tired of the fact that every time I need to write a macro for a different Office product, it's as if I have to relearn VBA.  (Can I get some cheese with this whine?)

I'm essentially looking for a turnkey solution, so the points on here are 350.  If the turnaround time is fast, I'd be happy to raise the points...

Option Compare Database

     Option Explicit
     Dim intPrintCounter As Integer
     Dim intNumberRepeats As Integer

   
Private Sub Report_Open(Cancel As Integer)

     Dim db As Database
     Dim tbl As Recordset
     Dim NumberRecords As Integer
     Dim i As Integer
     Dim intNumberRepeats As Variant
 
  intPrintCounter = 1
  intNumberRepeats = Forms!tblDistList!QTY    'QTY is the field wherein the number of prints are defined.
                                                                      'This is consistent in the qrySTP query, and the rptCoverPage report.
  Set db = CurrentDb
  Set tbl = db.OpenRecordset("qrySTP")             'qrySTP is the open qry wherein all the report recipients are stored.
 
  NumberRecords = tbl.RecordCount
  For i = 0 To NumberRecords
   intNumberRepeats(i) = Forms!tblDistList!QTY(i)
   Next i

End Sub






Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

   If i < NumberRecords Then
     If intPrintCounter < intNumberRepeats(i) Then
         intPrintCounter = intPrintCounter + 1
        ' Do not advance to the next record.
         Me.NextRecord = False
     Else
        ' Reset intPrintCounter and advance to next record.
         i = i + 1
         intPrintCounter = 1
         Me.NextRecord = True
     End If
   End If

End Sub


0
Comment
Question by:holosimexchange
  • 9
  • 9
18 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
You will need to set any parameters manually if you are opening a query that contains parameters - have a look here:
http://www.mvps.org/access/queries/qry0003.htm
0
 

Author Comment

by:holosimexchange
Comment Utility
Looks like a good resource Shane.  Thanks for the tip, but I'm back to that "Keyword not found" thing with the QueryDef variable type.  The system seems to recognize the QueryDef function, though, and I'm not getting any errors yet.  With luck, this may help in getting this job done.

 
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Did you add the reference to the DAO library? That should resolve the keyword not found.
0
 

Author Comment

by:holosimexchange
Comment Utility
The References seem to check out.  I have the DAO and Access 9.0 Object Library selected (among others.)  I'm not getting an error from the QueryDef function.  It seems to be passing that variable.  I'm still getting an error with my array.

 I think my problem is just that I'm not sure I understand Edith's use of the array above.  I understand the algorithm of using an array to store the results of the query so that each record can be printed individually.  I'm not sure how that connects to the second procedure, though.  I think I may need to back up and start again on this task.

I really just need to print each record of rptCoverPage based on the value of QTY in each record.  I had inially envisioned this as a customized button on the toolbar.  In theory, as long as the report is open, I can reference the report as my dynaset and print each record consecutively with a DoCMD.Printout "rptCoverPage", , ,QTY...    right?  
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
What error are you getting - can you indicate the line that is failing?
0
 

Author Comment

by:holosimexchange
Comment Utility
On the line that sets the array,

 intNumberRepeats(i) = Forms!frmDistList!QTY(i)

I'm getting an error from the system saying that I can't reference the latter as an array.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
The line is trying to reference a form - try this instead in the report's Open event:

Private Sub Report_Open(Cancel As Integer)

     Dim db As Database
     Dim tbl As Recordset
     Dim NumberRecords As Integer
     Dim i As Integer
     Dim intNumberRepeats() As Long
 
  intPrintCounter = 1
  Set db = CurrentDb
  Set tbl = db.OpenRecordset("qrySTP")
 
  NumberRecords = tbl.RecordCount
  ReDim intNumberRepeats(NumberRecords-1)
  For i = 0 To NumberRecords-1
   intNumberRepeats(i) = tbl!QTY
   tbl.MoveNext
  Next i
  tbl.Close
  Set tbl=Nothing
End Sub

Of course, you'll need to modify this to pass the correct parameters into qrySTP.
0
 

Author Comment

by:holosimexchange
Comment Utility
I'm still getting an error on the OpenRecordSet() function.

"Too Few Parameters.  Expected 1."

I was getting this error before and thought I had gotten around it by opening the query before running the Report.  That doesn't seem to be working now...

hmmm
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
What parameters does your query take?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:holosimexchange
Comment Utility
Aha, that's right, I need to set the variable for the query.  When the user accesses the query, the system prompts them for a Report Number (STPNo is the field name).  Of course, this occurs when a user accesses the Report as well, since the report is driven by the query.  

Ok, I'm showing my ignorance here.  I replaced the OpenRecordSet() function with the following line.

  Set tbl = db.OpenRecordset("qrySTP", Table!.tblDistList!.STPNo)

Now when I run the report, I get an "Invalid qualifier"  error.  'STPNo' is the field in the table (tblDistList) that the user needs to specify to return a query.  I've tried several types of syntax for this variable, to no avail.  Will the report still automatically prompt for the STP Number, or do I need to write code to generate a prompt and get that variable?  
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Your query has a criteria in it, I assume - can you post that criteria?
0
 

Author Comment

by:holosimexchange
Comment Utility
In the query list, under STPNo, I have the following criteria...

[Enter STP Number]

That's the prompt that appears when a user accesses the query/report.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 450 total points
Comment Utility
I haven't tried this, but this might work - create a hidden textbox on your report bound to the QTY field (call it say txtQTY). Replace the OnOpen code with just this:

Private Sub Report_Open(Cancel As Integer)
  intPrintCounter = 1
End Sub

Now change the Print code to read:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

     If intPrintCounter < Me!txtQTY Then
         intPrintCounter = intPrintCounter + 1
        ' Do not advance to the next record.
         Me.NextRecord = False
     Else
        ' Reset intPrintCounter and advance to next record.
         i = i + 1
         intPrintCounter = 1
         Me.NextRecord = True
     End If

End Sub
0
 

Author Comment

by:holosimexchange
Comment Utility
Shane,

      I'm wondering if you could help me understand your approach.  It seems like you're having the system display each record a certain number of times in the Report Preview.  That would work great, because then the users can view the results, and use the print button to print the results (which would be the multiples of each record I need to print.)  However, the code above results in thousands of records, even when there should be less than 100 (See sample data set below).  I can't jump to the end of the report to get a specific number, for some reason.  It's posting alot more of each document than it's supposed to (sometimes 6 times more, sometimes 1000 times more).  I also can't find a pattern.  Sometimes it prints 32 times as many pages, sometimes it prints only one when it should print 6.   I tried sending it to the printer, and as I watched the number of pages being printed go up and up, I had to kill the print job after it passed 20,000 pages.  It's running the query just fine, and returning only the STP numbers I want, but the number of prints seems to be in error.

STPNo is the query parameter (there are several other responses that will return results.  All of them have the same issues.)
QTY is the number prints I need for each record.

STPNo      Name                          MailStop      QTY
503      DOC. SUPPORT          USK-839      11
503      REPOSITORY          USK-886      7
503      HGR. AF REPOSITORY          USK-805      5
503      NDE DATA CENTER          USK-823      10
503      D. BROWN                          USK-835      2
503      B. CIPOLLA                          USK-816      2
503      J. DAY  (HGR. AF)          USK-803      11
503      R. FOWLER          QS20                      11
503      A. FERTIG                          USK-807      11
503      D. POWELL                          USK-835      2
0
 

Author Comment

by:holosimexchange
Comment Utility
Shane, one more quick question...

where is 'i' affecting the code?  The only place it is being used is the 'i = i + 1' line.
Was I supposed to add some code from the previous procedure?

Also (ok, this is several questions...)  if I search the help files for Detail_Print, shouldn't I get some results?  I'm asking because I want to make sure the problem isn't going back to the References thing.  I have DAO and ADO checked under references.  Also, I'm trying to understand the Detail_Print and Report_Open subs better.  When are these activated?

Thanks for all your help so far.

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
I wasn't absolutely certain that the code would work, but the idea was that when the report prints, it looks at the number of times that the current detail section has been printed (intPrintCounter) and compares it to the number of times it needs to print (txtQTY).

Can you filter the report so that it only selects one record, and see if that produces the correct number? You can just set the printer to pause - you only need to see how many pages are being produced. Does the report contain any subreports?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Sorry, the i line is no longer necessary. As for searching the help file, look for "Print event" and "Open event".
0
 

Author Comment

by:holosimexchange
Comment Utility
Well how do you like that!!!
     I spent the afternoon looking through the help files to try and familiarize myself with the Report_Open and Detail_Print object events, and I think I figured it out!
    So now I understand your logic, Shane.  The Detail_Print function loops for each record in my table, so I don't need to set up a loop inside the function.  The only problem with the script above is that the variable intPrintCounter needs to be initialized at the beginning of each record, and the variable from the Report_Open sub was not being passed the Detail_Print sub.  (I put a stop on the procedure and ran it a few times, and the variable was <null> in the Detail_Print sub.)  That's why it wouldn't print the correct number for the first print, and it would print forever on the second print.  The first print started at <null> and confused the system.  After adding 1 several times, by the time the routine stepped to the next record (IF it stepped to the next record) it would already be more than the required variable, and it would never stop.  Which it didn't.  So there seemed to be no way to initialize the variable outside the loop of Detail_Print.  (How do you make a Global Variable in Access' VBA editor?)

  I discovered that the PrintCount argument in Detail_Print increments with every additional item in a section.  Then it resets to 1 when it goes to a new section.  So in this particular case, I don't even need to define a variable.

Option Compare Database

Private Sub Report_Open(Cancel As Integer)
  ' no scripts required
End Sub


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
     
    If PrintCount < Me!txtQTY Then
        Me.NextRecord = False
Else
        Me.NextRecord = True
    End If

End Sub

I'm sure there are plenty of Experts around here that would balk because I'm referencing the PrintCount argument directly, instead of using an ambiguous variable as a buffer.  And I don't have an error trap, in case one of the records sends an invalid value.  But hey, it works.
      And I'm kinda ticked that this works.  I showed it to my boss, and he's totally unimpressed.  He likes to see programs with lots of code.  I almost considered adding a bunch of variables and setting up a few loops that wouldn't feed anything.  But then I realized, with my luck, no matter how obscure I make the variable names, he'll probably name something in the Report the same as the variable and start getting errors.  Better that it works and looks simple than Having to go back and fix something with his coffee-breath over my shoulder.

Thanks a bunch to Shanesuebsahakarn.  You led me in the right direction.  And now people will be coming to me at work for macros and scripts in Access...      Hmmm...    Maybe I shouldn't be so thankful.  :)

Take Care,
HoloSim
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

772 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

10 Experts available now in Live!

Get 1:1 Help Now