Label Printing (A2k SP1)

Topic

New Reply

I created a report with the Access Wizard to print Avery 5160 labels (( 1″ X 2 5/8) 3 Columns 10 Rows), set to print across then down.

How can I have the report start printing at a specific row and column using VBA?

Thanks in advance

Reply | Quote

Replies

Put these code in a module.
The two Dim lines must be in the beginning of the module, before any other Function or Sub

Dim intLabelBlanks As Integer
Dim intBlankcount As Integer
Function LabelLayout(R As Report)
   If intBlankcount < intLabelBlanks Then
      R.NextRecord = False
      R.PrintSection = False
      intBlankcount = intBlankcount + 1
   End If
End Function
Function LabelInitialize()
   intBlankcount = 0
End Function
Function LabelSetup()
  intLabelBlanks = Val(InputBox$("Enter Number of blank labels to skip"))
  If intLabelBlanks < 0 Then intLabelBlanks = 0
End Function

In the design of the report, select the event property tab of the Report.
For the on Open event enter : =LabelSetup()
For the on Activate event enter : =LabelInitialize()
Select the event property tab of the Detail
for the on Print event enter : =LabelLayout([Reports]![ReportName])
Replace ReportName with the name of your report.

This code is a simplified version of the code of the Reports Sample Database from Microsoft available at :
Microsoft Access 2000 Sample Reports Available in Download Center (Q231851)

Reply | Quote

Thank You!

Your code works perfectly.
Another Lounge success story.

Thanks again

Reply | Quote

I know you already got a satisfactory solution, but I had a similar situation which I handled quite differently. On a Labels form, which allowed the user to select Mailing Labels, File Folder Labels, Shipping Labels, etc. I added a textbox for Starting Row (named StartRow) of the sheet of labels. My support personnel are very conscientious about saving EVERY PENNY so they asked for this. The default, of course, is 1. If they have a sheet with 2 rows of labels already removed they simply change the testbox to the value (number) “3”. In each saved report (1 for each type of label) I put the following code in the OnOpen property:
Me.ReportHeader.Height = (1440 * Forms!PrintLabels!StartRow) – 1440
There are no fields in any of the report headers. 1440 is the conversion between twips (whatever they are!) and inches.

It works perfectly. As all of us loungers have repeatedly discovered, this program always gives us more than one way to skin a cat!!!

Just food for thought
Kathi

Reply | Quote

That’s a nice trick, but if the column layout of the report is Down, then Across it won’t work.
And as you say “saving EVERY PENNY”, with your method you can loose one or two labels in a 3 column report.

Reply | Quote

This is Awesome!

It’s one of those things that I have been planning on looking into for several years but just never got around to it. Thank you.

Reply | Quote

The link in post 124,503 does not work anymore.
The Reports Sample Database can be now be found at :
Download details: Access 2000 Sample: Report Topics

Reply | Quote

I was just wondering if you had encountered a problem with the labels. I have one setup with 5160 also but occassionally, when i open the labels, it changes it layout to print out the labels all in the first left hand column only. All the records are there, but the template seems to have changed. Of course, then sometimes it opens like it should. I’ve tried creating a new one but still encounter the problem. Not sure.
-Youa

Reply | Quote