Labels - How to Print to Column 2?

[Pookaroo85]

I have a report for labels that feeds from a query. There are 10 labels that will be printed at a time. I am using label sheets that have 30 labels and consumes the leftmost column each time. I need to figure out how to select the column so that I don't waste the middle column. How do I start printing at label 1 or 11 on command? Thanks in advance!!

[PhilOfWalton]

Have a look at http://www.peterssoftware.com/ls.htm

I think if you start at label 11, you will get what yo want.

Phil

[twinnyfo]

I have a solution similar to what is described. Hey Phil, can you upload that zip file? I can't get to it from work!

Thanks.

[PhilOfWalton]

My pleasure

Cheers

Phil

[twinnyfo]

I hate this work network.

For those who can't get to this zip file, such as myself, the concept I use is simple.

Instead of printing column by column, I print row by row. I have a table of 30 blank records (yes, it sounds stupid, but stick with me). When I have anyone printing labels, they count how many labels have been used from the top of the page and they enter that into an InputBox(). The query for the labels is a UNION ALL that includes the same number of blank labels at the top fo the list. Then it prints, and the first "real" label prints in the proper location.

Very little code involved in mine, but I think the solution linked by Phil probably is a bit easier for the average Joe to use. I just wish I could get to the beast so I could work it into my DB.

Thanks, Phil!

[twinnyfo]

I was finally able to download it, and take a look at this. This is very simple and yet incredibly clever.

I would have done things a bit slightly differenlty, but it certainly works and is completely transparent to the user. Much easier than my method.

Great find, Phil! I learned something today!

[Pookaroo85]

Thanks all! This is great!!

One note... You have to have the report page set up as Column Layout = Across, then Down. Otherwise, it doesn't work.

[twinnyfo]

Yes - across then down.

I'm glad Phil found that little treasure. I plan on doing a few modifications to make it a bit easier to follow.

[twinnyfo]

Friends,

I made a few mods to standardize the appearance and make the logic a bit more straightforward . For those who can't download the attachment, or for those who simply want the code behind this trick, here is what I have come up with.

Of course, I have given proper credit within the code to the original source, to whom I am thankful for their contributions.

Option Compare Database
Option Explicit
'  Adapted from Peter's Software
'  http://www.peterssoftware.com
'  info@peterssoftware.com
'
'  Allows users to specify how many blank labels to print
'  at the top of a sheet of labels and print multiple copies
'
'  Usage:
'    PrintLabels Me, T/F, Cancel  - From your Report's OnOpen Event Procedure.
'                                   True/False indicates if you want to allow
'                                   the user to print multiple copies of labels
'    InitLabels                   - From your Report's ReportHeader OnFormat
'                                   Event Procedure. Create a ReportHeader if
'                                   you don't have one already. Set the Report
'                                   Header Section Height to 0.
'    LabelOnPrint Me              - From your Report's Detail OnPrint Event
'                                   Procedure.

'Module variables
Private intBlankLabels  As Integer
Private intBlankCount   As Integer
Private intCopies       As Integer
Private intCopiesCount  As Integer

'Prompts user for number of blank labels at the top of sheet
'Also, variably prompts user for number of copies.
'Sets variables for the OnFormat event procedure to handle
Public Sub PrintLabels(rpt As Report, _
                       fCopies As Boolean, _
                       ByRef Cancel As Integer)
On Error GoTo EH
    Dim strPrompt   As String
    Dim strLabels   As String

    'Prompt user for number of blank labels to print at top
    intBlankLabels = -1
    strPrompt = _
        "How many Blank Labels do you want " & _
        "to print at the top of the " & _
        "page (count across and then down)?" & _
        vbCrLf & vbCrLf & _
        "Please enter a number between 0 and 19."
        'You may change the "19" to suit the maximum
        'number of lables you have on a sheet
    Do While intBlankLabels < 0 _
        Or intBlankLabels > 19
        strLabels = InputBox(Prompt:=strPrompt, _
                             Title:="Blank Labels")
        If IsNumeric(strLabels) Then
            intBlankLabels = CInt(strLabels)
        End If
    Loop

    intCopies = -1
    'Prompt user for number of copies of each label to print
    If fCopies Then
        strPrompt = _
            "How many copies of each label do you want to print?" & _
            vbCrLf & vbCrLf & _
            "Please enter a number between 1 and 20."
        Do While intCopies < 0 _
            Or intCopies > 20
            strLabels = InputBox(Prompt:=strPrompt, _
                                 Title:="Number of Copies", _
                                 Default:=1)
            If IsNumeric(strLabels) Then
                intCopies = CInt(strLabels)
            End If
        Loop
    End If

    Exit Sub
EH:
    Call msgError("setting the Label options")
    Exit Sub
End Sub

Public Sub InitLabels()
    intBlankCount = 0
    intCopiesCount = 1
End Sub

'Print a specified number of blank detail sections.
'When done, print the specified number of copies of each label
Public Sub LabelOnPrint(rpt As Report)
On Error GoTo EH

    If intBlankCount < intBlankLabels Then
        'Leave a blank detail section without skipping a record
        rpt.NextRecord = False
        rpt.PrintSection = False
        intBlankCount = intBlankCount + 1
    Else
        If intCopiesCount < intCopies Then
            rpt.NextRecord = False
            intCopiesCount = intCopiesCount + 1
        Else
            intCopiesCount = 1
        End If
    End If

    Exit Sub
EH:
    Call msgError("printing the Label Detail")
    Exit Sub
End Sub

Private Sub msgError(ErrorText As String)
    Dim strPrompt   As String
    Dim intButtons  As Integer
    Dim strTitle    As String

    strPrompt = _
        "There was an error " & ErrorText & "!" & _
        vbCrLf & vbCrLf & _
        "Error: " & Err.Number & vbCrLf & _
        "Description: " & Err.Description & _
        vbCrLf & vbCrLf & _
        "Please contact your Database Administrator."
    intButtons = vbOKOnly + vbCritical
    strTitle = "WARNING!!!"
    Call MsgBox(Prompt:=strPrompt, _
                Buttons:=intButtons, _
                Title:=strTitle)

End Sub

Keep in mind that you must insert all three of the described calls into the appropriate events of your report.

This is an easy way to dazzle your friends!

[Pookaroo85]

This is a great tool and looks great in print preview. But, it jumps back to label 1 when I actually print the labels. Any ideas?

[twinnyfo]

Perhaps you can explain what you are doing to cause this to happen. It works fine for me in both print preview and in printing.

Not sure why it would do that....