By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,668 Members | 1,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,668 IT Pros & Developers. It's quick & easy.

Labels - How to Print to Column 2?

P: 34
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!!
Jan 31 '19 #1

✓ answered by 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

Share this Question
Share on Google+
10 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Feb 1 '19 #2

twinnyfo
Expert Mod 2.5K+
P: 3,282
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.
Feb 1 '19 #3

PhilOfWalton
Expert 100+
P: 1,430
My pleasure

Cheers

Phil
Attached Files
File Type: zip ls30.zip (288.5 KB, 16 views)
Feb 1 '19 #4

twinnyfo
Expert Mod 2.5K+
P: 3,282
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!
Feb 1 '19 #5

twinnyfo
Expert Mod 2.5K+
P: 3,282
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!
Feb 1 '19 #6

P: 34
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.
Feb 1 '19 #7

twinnyfo
Expert Mod 2.5K+
P: 3,282
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.
Feb 1 '19 #8

twinnyfo
Expert Mod 2.5K+
P: 3,282
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. '  Adapted from Peter's Software
  4. '  http://www.peterssoftware.com
  5. '  info@peterssoftware.com
  6. '
  7. '  Allows users to specify how many blank labels to print
  8. '  at the top of a sheet of labels and print multiple copies
  9. '
  10. '  Usage:
  11. '    PrintLabels Me, T/F, Cancel  - From your Report's OnOpen Event Procedure.
  12. '                                   True/False indicates if you want to allow
  13. '                                   the user to print multiple copies of labels
  14. '    InitLabels                   - From your Report's ReportHeader OnFormat
  15. '                                   Event Procedure. Create a ReportHeader if
  16. '                                   you don't have one already. Set the Report
  17. '                                   Header Section Height to 0.
  18. '    LabelOnPrint Me              - From your Report's Detail OnPrint Event
  19. '                                   Procedure.
  20.  
  21. 'Module variables
  22. Private intBlankLabels  As Integer
  23. Private intBlankCount   As Integer
  24. Private intCopies       As Integer
  25. Private intCopiesCount  As Integer
  26.  
  27. 'Prompts user for number of blank labels at the top of sheet
  28. 'Also, variably prompts user for number of copies.
  29. 'Sets variables for the OnFormat event procedure to handle
  30. Public Sub PrintLabels(rpt As Report, _
  31.                        fCopies As Boolean, _
  32.                        ByRef Cancel As Integer)
  33. On Error GoTo EH
  34.     Dim strPrompt   As String
  35.     Dim strLabels   As String
  36.  
  37.     'Prompt user for number of blank labels to print at top
  38.     intBlankLabels = -1
  39.     strPrompt = _
  40.         "How many Blank Labels do you want " & _
  41.         "to print at the top of the " & _
  42.         "page (count across and then down)?" & _
  43.         vbCrLf & vbCrLf & _
  44.         "Please enter a number between 0 and 19."
  45.         'You may change the "19" to suit the maximum
  46.         'number of lables you have on a sheet
  47.     Do While intBlankLabels < 0 _
  48.         Or intBlankLabels > 19
  49.         strLabels = InputBox(Prompt:=strPrompt, _
  50.                              Title:="Blank Labels")
  51.         If IsNumeric(strLabels) Then
  52.             intBlankLabels = CInt(strLabels)
  53.         End If
  54.     Loop
  55.  
  56.     intCopies = -1
  57.     'Prompt user for number of copies of each label to print
  58.     If fCopies Then
  59.         strPrompt = _
  60.             "How many copies of each label do you want to print?" & _
  61.             vbCrLf & vbCrLf & _
  62.             "Please enter a number between 1 and 20."
  63.         Do While intCopies < 0 _
  64.             Or intCopies > 20
  65.             strLabels = InputBox(Prompt:=strPrompt, _
  66.                                  Title:="Number of Copies", _
  67.                                  Default:=1)
  68.             If IsNumeric(strLabels) Then
  69.                 intCopies = CInt(strLabels)
  70.             End If
  71.         Loop
  72.     End If
  73.  
  74.     Exit Sub
  75. EH:
  76.     Call msgError("setting the Label options")
  77.     Exit Sub
  78. End Sub
  79.  
  80. Public Sub InitLabels()
  81.     intBlankCount = 0
  82.     intCopiesCount = 1
  83. End Sub
  84.  
  85. 'Print a specified number of blank detail sections.
  86. 'When done, print the specified number of copies of each label
  87. Public Sub LabelOnPrint(rpt As Report)
  88. On Error GoTo EH
  89.  
  90.     If intBlankCount < intBlankLabels Then
  91.         'Leave a blank detail section without skipping a record
  92.         rpt.NextRecord = False
  93.         rpt.PrintSection = False
  94.         intBlankCount = intBlankCount + 1
  95.     Else
  96.         If intCopiesCount < intCopies Then
  97.             rpt.NextRecord = False
  98.             intCopiesCount = intCopiesCount + 1
  99.         Else
  100.             intCopiesCount = 1
  101.         End If
  102.     End If
  103.  
  104.     Exit Sub
  105. EH:
  106.     Call msgError("printing the Label Detail")
  107.     Exit Sub
  108. End Sub
  109.  
  110. Private Sub msgError(ErrorText As String)
  111.     Dim strPrompt   As String
  112.     Dim intButtons  As Integer
  113.     Dim strTitle    As String
  114.  
  115.     strPrompt = _
  116.         "There was an error " & ErrorText & "!" & _
  117.         vbCrLf & vbCrLf & _
  118.         "Error: " & Err.Number & vbCrLf & _
  119.         "Description: " & Err.Description & _
  120.         vbCrLf & vbCrLf & _
  121.         "Please contact your Database Administrator."
  122.     intButtons = vbOKOnly + vbCritical
  123.     strTitle = "WARNING!!!"
  124.     Call MsgBox(Prompt:=strPrompt, _
  125.                 Buttons:=intButtons, _
  126.                 Title:=strTitle)
  127.  
  128. 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!
Feb 4 '19 #9

P: 34
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?
Feb 15 '19 #10

twinnyfo
Expert Mod 2.5K+
P: 3,282
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....
Feb 16 '19 #11

Post your reply

Sign in to post your reply or Sign up for a free account.