Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pages of Labels for one person

    I need the VB code to stop a procedure from becoming the monster it is. I am creating a couple of pages of printed labels for one person. At this point I have to tell the File...Print...How many Pages command just how many I need, because my VB knowledge is very limited. Here is my scenario:
    I have a query for one person.
    I base the report labels around that one person
    In design view of my label, I right-click on a clean part of the label grid. I choose Properties.
    I choose the On Print command from Properties, then choose the code builder.
    I type between the sub and end sub lines this code:

    NextRecord=False

    How should I phrase this better and complete my code if I would like to tell it to stop, say after 2 or 3 pages of printing?

    Any help would be so appreciated. Thanks a bunch. <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Padgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pages of Labels for one person

    This method does not require any VB code. It was a hint I read some time ago. Create a new table with two fields, make one of the fields an autonumber field. In my instructions, I will call this table, tblLabelQty. Add records to your new table, you can simply put an x in each row of the table so that the autonumber field increments to the highest number of labels you may want at any one time. In my example, I am going to call the autonumber field Qty.

    Create a query and add the table or query where your data resides and add the new table called tblLabelQty. Do not attempt to join the two tables. Add all the fields you need for your labels and the Qty field from tblLabelQty. For the criteria create a parameter query by placing the prompt [Enter Last Name] under your LastName field and if you have more than one person with the same last name you may want to create a prompt for first name too. For the criteria under the Qty field enter the following prompt <=[Enter # of Labels Needed]. Save your query and build your label report on this query.

    When you want to print your report, double click on the report name and after you have filled in the prompts for a Last Name and for the number of Labels needed you will have that number of labels for the person you specified.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pages of Labels for one person

    Judy,
    This sounds like an absolutely perfect idea. But I walked through the steps you gave me about 6 times before i realized I must be missiing something. Each time I run the label wizard from the query you told me about, it prompts me for last name, and how many labels i want...which would seem okay, if i didn't have to put how many i wanted to begin with in the tblLabelqty already (marked by x's according to how many I'd like). Why do I need this second box...it ignores my requested number of labels anyway, and prints however many x's i have in the 2 field table.
    Did I miss something...help <img src=/S/help.gif border=0 alt=help width=23 height=15>
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pages of Labels for one person

    You can save yourself a bit of time by simply making the table of numbers a single field table, the field being Number field set to Integer. Then you can populated the table with the numbers 1 to 30, or 1 to 60 etc. You don't need to have an autonumber field by any means, and you could even use Excels "Fill Series" feature to make the list of numbers then pasted append them to the access table.

    The hint you remember reading is a good one, the reason this method works is because of the cartesian product created by not joining the tables. One of the few times not joining tables is very helpful.

    FWIW

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pages of Labels for one person

    Thanks Tom,
    I will try this, but again was hoping for the 1 code line in the On Print event that would stop printing after the criteria I would set in the VB code.

    example:

    NextRecord=False
    (Then what...because I can't turn it off. The only way to create 3 pages or 7 pages of labels with this method is to leave the code a one liner like I have it, and use the File...Print...How Many pages command...If anyone has the VB code knowledge, would still like the original question answered, and I truly thank you Judy and Tom for helping me out here...will try Toms method next.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pages of Labels for one person

    One field would work but I thought the autonumber field would be less trouble than typing in 150 numbers in case you needed as much as 5 pages of 30 labels to a page.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pages of Labels for one person

    If your prompt is based on the Qty field (which should be the field containing your numbers) once you type in the number 30 in answer to the query prompt, that is how many labels you should get even if you have numbers going as high as 500 in your table.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pages of Labels for one person

    Judy,

    Well it works, but only if i have more X's in the "dummy" table "tblLabelQty", then I will ever need in pages for labels.
    I will use this method Judy until I learn more VBA...thanks so much for hanging with me on this one. Your dedicated and very kind.
    Padgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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