Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Apr 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Label Printing (A2k SP1)

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Printing (A2k SP1)

    Put these code in a module.
    The two Dim lines must be in the beginning of the module, before any other Function or Sub
    <pre>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</pre>

    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 :
    <A target="_blank" HREF="http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q231851">Microsoft Access 2000 Sample Reports Available in Download Center (Q231851)
    </A>
    Francois

  3. #3
    Lounger
    Join Date
    Apr 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Printing (A2k SP1)

    Thank You!

    Your code works perfectly.
    Another Lounge success story.

    Thanks again

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Decatur, AL
    Posts
    53
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Label Printing (A2k SP1)

    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

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Printing (A2k SP1)

    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.
    Francois

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Printing (A2k SP1)

    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

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Label Printing (A2k SP1)

    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.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Printing (A2k SP1)

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

Posting Permissions

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