Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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 < Me.NumberOfCopies Then
    Me.NextRecord = False
    End If
    Else
    lngCurrentCount = 1
    lngPreviousID = Me.ID
    If Me.NumberOfCopies > 1 Then
    Me.NextRecord = False
    End If
    End If
    End Sub

  3. #3
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •