Multiple labels from each record (Access 97, Win 2000)

Topic

New Reply

I’m creating a report to print labels for a set of records. Each record’s label needs to be duplicated a number of times, the number coming from a field within the record itself.

The only solution I’ve thought of so far is to write some VBA to duplicate the records in a table. However, the data is from a live link to another database, and it seems a shame to break that link.

Is there any way of duplicating records on the fly, perhaps through a query? Any suggestions gratefully received!

Reply | Quote

Replies

You can do this in the label report itself. Besides the field that specifies the number of copies, you need a field that uniquely identifies each record; if you have a single-field primary key (for example an autonumber field), that will serve.

Here is code for the On Print event of the detail section of the label report. I have assumed that the unique identifier is a number field named ID, and that the field specifying the number of copies is named NumberOfCopies. Replace these names with the ones used in your table.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static lngPreviousID As Long
Static lngCurrentCount As Long
If Me.ID = lngPreviousID Then
lngCurrentCount = lngCurrentCount + 1
If lngCurrentCount 1 Then
Me.NextRecord = False
End If
End If
End Sub

Reply | Quote

Perfect! That works really well. I’ll have to study the code to find out how. Does the “Me.NextRecord = False” force the report to call the event again?

I knew it had to be possible, but I’m pretty sure I would never have stumbled upon that solution by myself. Certainly the hours I’d spent looking through Help etc. hadn’t got me anywhere.

Thank you so much for your help!

Reply | Quote

Normally, the Detail section is displayed/printed only once for each record in the record source of the report. Setting NextRecord = False prevents the report from moving on to the next record, so that the same record is displayed/printed again. This is perceived as a duplicate. The code I posted uses a static variable lngCurrentCount to keep track of how many times the record has been displayed; this is compared to the field that specifies the number of copies. “Static” means that the variable keeps its value after the procedure has finished (as long as the report stays open.)

Reply | Quote

Normally, the Detail section is displayed/printed only once for each record in the record source of the report. Setting NextRecord = False prevents the report from moving on to the next record, so that the same record is displayed/printed again. This is perceived as a duplicate. The code I posted uses a static variable lngCurrentCount to keep track of how many times the record has been displayed; this is compared to the field that specifies the number of copies. “Static” means that the variable keeps its value after the procedure has finished (as long as the report stays open.)

Reply | Quote

Perfect! That works really well. I’ll have to study the code to find out how. Does the “Me.NextRecord = False” force the report to call the event again?

I knew it had to be possible, but I’m pretty sure I would never have stumbled upon that solution by myself. Certainly the hours I’d spent looking through Help etc. hadn’t got me anywhere.

Thank you so much for your help!

Reply | Quote

You can do this in the label report itself. Besides the field that specifies the number of copies, you need a field that uniquely identifies each record; if you have a single-field primary key (for example an autonumber field), that will serve.

Here is code for the On Print event of the detail section of the label report. I have assumed that the unique identifier is a number field named ID, and that the field specifying the number of copies is named NumberOfCopies. Replace these names with the ones used in your table.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static lngPreviousID As Long
Static lngCurrentCount As Long
If Me.ID = lngPreviousID Then
lngCurrentCount = lngCurrentCount + 1
If lngCurrentCount 1 Then
Me.NextRecord = False
End If
End If
End Sub

Reply | Quote