Printing out duplicate records automatically

[junker1234]

I use Access to generate receiving tags for orders that we receive
using a report based on a parameter query that lists all items on a
single invoice.

The problem is that sometimes a given item will have a quantity greater
than one. When I run the report it only prepares one tag for this item
even though I will need more than one tag (because the items come in
different boxes). Printing out the report multiple times works, but it
is time consuming and wastes paper.

Is there any way to have Access duplicate a record a certain number of
times in the report?


Example

Invoice #11111
Item: Hat
Qty:3

Would show only:
Hat Inv#11111

But I want it to show:
Hat Inv#11111
Hat Inv#11111
Hat Inv#11111

so that I can tage each item.

Thanks for any help!

 

[Tom Wickerath]

Hi Junker,

Here is an example that might just work for you. It is a variation of the
method shown in the following KB article:

HOW TO: Repeat Report Records a Specified Number of Times (in Access 2000)
http://support.microsoft.com/?id=207664

However, the method shown in this KB article allows you to print the entire
report X number of times. That's probably fine if you are printing only
Invoice number at a time, controlled by your parameter query.

For my example, I used the Northwind sample database (Northwind.mdb), a copy
of which is likely installed on your hard drive already. Create a new query.
Dismiss the Add Table dialog. In query design view, click on View > SQL View.
Replace the word SELECT with the following SQL statement:

SELECT [Order Details].OrderID, Products.ProductName,
[Order Details].UnitPrice, [Order Details].Quantity
FROM Products
INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
WHERE ((([Order Details].OrderID) In (10248,10249,10250)))
ORDER BY [Order Details].OrderID, [Order Details].Quantity;

I'm purposely limiting the recordset to three OrderID's for this
demonstration. Run the query. Change the quantities indicated to convenient
numbers between 1 and 5 for each record.

Create a new report. Add the ProductName, UnitPrice and Quantity fields to
the Detail section. Click on View > Sorting and Grouping. Add the OrderID
field with Group Header = Yes. Close the Sorting and Grouping dialog. Add the
OrderID field to the OrderID Header. Cut all associated labels, one at a
time, and paste them into the Page Header.

Now add the following code behind the report:

Option Compare Database
Option Explicit

Dim intPrintCounter As Integer
Dim intNumberRepeats As Integer

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


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
intNumberRepeats = Me.Quantity
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
' Note: intNumberRepeats and intPrintCounter are initialized
' in the report's OnOpen event.
If intPrintCounter < intNumberRepeats Then
intPrintCounter = intPrintCounter + 1
' Do not advance to the next record.
Me.NextRecord = False
Else
' Reset intPrintCounter and advance to next record.
intPrintCounter = 1
Me.NextRecord = True
End If

End Sub


Run the report. I think that gives you the desired effect.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[junker1234]

Thank you so much for your help!

It does exactly what I needed it to do!

I really need to learn how to program so I can do this stuff myself.

Thanks again

 

[Tom Wickerath]

You're very welcome. I'm glad I could help!


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________