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
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)
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
' Reset intPrintCounter and advance to next record.
i = i + 1
intPrintCounter = 1
Me.NextRecord = True