Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Labels (Access 2000)

    This is probably a simple matter, but it's got me stymied.

    I'm printing multiple labels for each input record in an underlying recordset. The record has a field (number of labels) which determines the number of labels to be printed. Then I can print 1 of 9, 2 of 9, etc. on each label (assuming the field contained a 9).

    By setting me.nextrecord = false in the detail format event for every label except the last one I'm able to achieve what I want. I can print the required number of labels as well as the appropriate legent (x of y). Well, almost. At the end of each sheet of labels I seem to be missing a label before the next page. In other words, if the last label on a sheet is 10 of 14, then the first label on the next sheet is 12 of 14, rather than the expected 11 of 14. Everything works fine in the middle of the sheet; the problem only occurs at the start of a new sheet.

    The documentation seems to indicate that setting me.nextrecord to false causes a move to the next print section. My guess is that at the end of the sheet, it's advancing to the page footer section of the first page, then to the page header section of the next page. However, I'm at a loss as to how to correct this problem.

    Any suggestions would be greatly appreciated.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Labels (Access 2000)

    I have always had problems with labels. So my question is can you print a basic set of labels OK. What I mean is, can you select 28 records with a query and print them successfully on two pages of 14?

  3. #3
    New Lounger
    Join Date
    Jun 2001
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Labels (Access 2000)

    Yes, normal label printing works just fine.

    But I think I've resolved the problem, thanks to a hint in the Access 2000 Developers Handbook. In order for this process to work properly, the code needs to be in the onPrint event, not in the onFormat event. I'm not quite sure I understand the reasoning behind this distinction, but moving the code into the onPrint event made the problem go away!!

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Labels (Access 2000)

    I have been working on this for a long time, and come up dry here....maybe you can help me, you seem to have figured out a solution, but am confused.
    I query one person out of my table to create multiple labels for that person. I base the label report on this query for one person, say Bill Brown...when I choose the OnPrint line for this label, I have always typed:
    NextRecord=False

    But the problem being, it doesn't QUIT...if I don't remind myself to go to File...Print...How many pages, and type in a Number then it prints until I shut printer off, or run out of paper one. How are you stopping it to print only a certain amount of pages, with the code that you have?
    Appreciate any help you can give.
    NMPadgett
    PS, I have used many functions, tips, trys, dummie tables, parameter setups, and I like the NextRecord = False line the best, if I can just find a way to finish the code to stop its loop when I say STOP at a certain amount of pages.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Access Labels (Access 2000)

    You need to use two variables: one to hold the number of labels you want to print, and one to hold the running count of printed labels. You can set the first variable to (number of pages to print)*(number of labels per page).

    For example, say lngCopiesToPrint is the number of labels to print, and lngCopiesPrinted is the running count.

    In the OnPrint event of the detail section, use code like the following:

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If lngCopiesPrinted < (lngCopiesToPrint - 1) Then
    NextRecord = False
    lngCopiesPrinted = lngCopiesPrinted + 1
    Else
    lngCopiesPrinted = 0
    End If
    End Sub

    If you're going to print labels for one person only, you can omit the Else part - it's meant to reset the running count for the next record.

    See HOW TO: Skip Used Mailing Labels and Print Duplicates in Access 2000 for more info.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Found it...Code for Multiple Labels

    Code for Multiple Labels
    (Using parameters for Lname & Count) ' To be pasted into the Detail_Print event of the report label, and fieldnames listed are only for examples such as Lname & Count, and tbl name Employees. Replace these with your fields and tbl names.

    Option Compare Database
    Dim RunUntil As Boolean
    Dim CountRequest As String
    Dim I As Long
    Dim LNameRequest As String


    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If RunUntil = False Then
    I = I + 1
    If I <> Int(CountRequest) Then
    NextRecord = False
    Else
    RunUntil = True
    End If
    End If
    End Sub

    Private Sub Report_Open(Cancel As Integer)
    Dim NameOK As VbMsgBoxResult
    Dim rsLabels As New Recordset
    rsLabels.ActiveConnection = CurrentProject.Connection
    I = 1
    Do Until I = 0
    LNameRequest = InputBox("Enter Last Name", "Last Name")
    rsLabels.Open "Select * from [Employees] where [Last Name]= '" & LNameRequest & "'"
    If rsLabels.EOF = True And rsLabels.BOF = True Then
    NameOK = MsgBox(LNameRequest & " was not found in the table", vbOKOnly, "Error")
    rsLabels.Close
    Else
    Filter = "[Last Name] = '" & LNameRequest & "'"
    FilterOn = True
    I = 0
    rsLabels.Close
    End If
    Loop

    I = 1
    Do Until I = 0

    CountRequest = InputBox("How many labels do you want to make?", "Labels", 20)
    If IsNumeric(CountRequest) = True Then
    If Int(CountRequest) > 0 Then
    RunUntil = False
    I = 0
    Else
    MsgBox "You must enter a positive number.", vbOKOnly, "Error"
    End If
    Else
    MsgBox "You must enter a positive number.", vbOKOnly, "Error"
    End If
    Loop
    End Sub


    HOPE THIS HELPS...IT'S BEAT ME UP FOR YEARS...CREDIT GOES TO A STUDENT OF MY ADV ACCESS CLASS (Camp LeJeune), and when her name comes back to me...gosh I forget easily...I will come in and shout it!!!! Hope this helps others...beats the heck out of creating dummy tables. And this one also prompts for parameter info.
    NMPadgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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