Skippping used mailing labels

[KevCB226]

Hi

I have created a report for labels using the Label Wizard, and found the code on the MS KB about skipping the labels that have already been used, and printing on the next one along.

Page on MS KB

When I try using this code in Access 2003 however, it seems to go into some sort of loop, and produces 100+ pages for the report when I try and skip 1 label for example.
Can anyone help me get this working for 2003?

Here's my module code, same as on the site above:

'********************************************************* 
  'Declarations section of the module.
'*********************************************************
Option Compare Database
Option Explicit
Dim LabelBlanks&
Dim LabelCopies&
Dim BlankCount&
DimCopyCount&
'=========================================================
  ' The following function will cause an input box to
  ' display when the report is run that prompts the user
  ' for the number of used labels to skip and how many
  ' copies of each label should be printed.
'=========================================================
Function LabelSetup()
   LabelBlanks& = Val(InputBox$("Enter number of used labels to skip"))  
   LabelCopies& = Val(InputBox$("Enter number of copies to print"))
   If LabelBlanks& < 0 Then
      LabelBlanks& = 0
   If LabelCopies& < 1 Then
      LabelCopies& = 1
End Function
'=========================================================
   ' The following function sets the variables to a zero
'=========================================================
Function LabelInitialize()
   BlankCount& = 0
   CopyCount& = 0
End Function
'=========================================================
   ' The following function is the main part of this code
   ' that allows the labels to print as the user desires.
'=========================================================
Function LabelLayout(R As Report)
   If BlankCount& < LabelBlanks& Then
      R.NextRecord = False
      R.PrintSection = False
      BlankCount& = BlankCount& + 1
   Else
      If CopyCount& < (LabelCopies& - 1) Then
         R.NextRecord = False
         CopyCount& = CopyCount& + 1
      Else
         CopyCount& = 0
      End If
   End If
End Function

Thanks

 

[ghudson]

I just converted my old Access 97 sample to Access 2003 and it worked fine. Here is the code from my Access 2003 sample...

    Dim intLabelBlanks As Integer
    Dim intLabelCopies As Integer
    Dim intBlankCount As Integer
    Dim intCopyCount As Integer
    
  '==========================================================
     ' The following function will cause an inputbox to
     ' display when the report is run that prompts the user
     ' for the number of used labels to skip and how many
     ' copies of each label should be printed.
  '===========================================================
    
Public Function LabelSetup()
    
    intLabelBlanks = Val(InputBox$("Enter number of blank labels to skip" & Chr(13) & Chr(13) & "Key 0 - 29", "Skip Blank Labels"))
    intLabelCopies = Val(InputBox$("Enter number of labels to print" & Chr(13) & Chr(13) & "Key 1 - 30", "Print Manual Labels"))
    If intLabelBlanks < 0 Then intLabelBlanks = 0
    If intLabelCopies < 1 Then intLabelCopies = 1
    
    'MsgBox "Don't forget to load your labels into the printer." & Chr(13) & Chr(13) & "You should use Avery labels for the best results.  Avery 5160 for ''Laser'' printers or Avery 8160 for ''Ink Jet'' printers." & Chr(13) & Chr(13) & "You can use any brand of labels but the label sheet must be for thirty labels by 1'' x 2-5/8''." & Chr(13) & Chr(13) & "Click OK to begin printing your labels or click Cancel to stop.", vbInformation + vbOKCancel, "Insert Labels"

End Function
    
  '===========================================================
     ' The following function sets the variables to a zero
  '===========================================================
    
Public Function LabelInitialize()
    
    intBlankCount = 0
    intCopyCount = 0
    
End Function
    
  '===========================================================
     ' The following function is the main part of this code
     ' that allows the labels to print as the user desires.
  '===========================================================
    
Public Function LabelLayout(R As Report)
    
    If intBlankCount < intLabelBlanks Then
        R.NextRecord = False
        R.PrintSection = False
        intBlankCount = intBlankCount + 1
    Else
        If intCopyCount < (intLabelCopies - 1) Then
            R.NextRecord = False
            intCopyCount = intCopyCount + 1
        Else
            intCopyCount = 0
        End If
    End If
    
End Function

 

[KevCB226]

Thanks, I've just tried your code, but I'm now getting a type mismatch error now, on the code behind the report.
I've highlighted the line it refers to.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
      LabelInitialize
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
      [COLOR="Red"]LabelLayout (Reports![LabelsForConferencing])[/COLOR]
End Sub

Private Sub Report_Open(Cancel As Integer)
      LabelSetup
End Sub

 

[ghudson]

Try

Call LabelLayout([Reports]![LabelsForConferencing])

 

[KevCB226]

Tried that and it's still going into some sort of loop, where I can just keep clicking forward over 100+ pages.

I've zipped it up anyway, so if anyone could take a look at it, then that would be great.

View attachment LabelTest.zip

Anyway, thanks for the help so far ghudson.

 

[KevCB226]

Has anyone been able to look at this, because I could do with trying to get it to work for a db I'm creating at work.

Thanks

 

[ghudson]

Your example has linked tables so it is impossible for us to test your db unless you include some live data.

 

[KevCB226]

Your example has linked tables so it is impossible for us to test your db unless you include some live data.

Woops, so it has. I'm such a noob, sometimes

I've imported the tables from the server now, so it should work.

View attachment LabelTest.zip