Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    PA
    Posts
    4

    Unanswered: Printing labels in Access

    I am having trouble printing barcode labels. I have developed a barcode label that prints a label for each order that we get, however the quantities of each order vary and I want the report to automatically kick out the number of labels that equals the quantity ordered. Is this possible, and if it is can someone give me some pointers. Thanks for the help!!!
    Last edited by Trumpright; 03-06-04 at 08:47.

  2. #2
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239
    Please have a look at my thread:

    [thread]
    http://www.dbforums.com/microsoft-ac...ml#post6431900

    Sincerely
    Vestigo

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think you'll need to develop a routine (as I think vestigo did in his example), which essentially appends to a temp type table duplicating the same info you want repeated X amount of times where X is the quantity.

    Without speaking for Vestigo, I believe he's doing essentially the same thing.

    I usually open a recordset in vba code and use a For Next loops based on the quantity to append to a temp type table (and then open the report which is based off the temp table.)

    Here's an example:

    (done from memory so pardon any syntax errors. Also untested but you should get the idea.)

    Function PopulateTempTable
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    Dim strSQL as string
    Dim Qty as variant
    Dim X as integer
    strSQL = "Select * from MyTableNameOrQueryName"
    rs.open strSQL, currentproject.connection,adopenkeyset,adlockreado nly
    'check for no records
    if rs.eof and rs.bof then
    msgbox "No recs"
    rs.close
    set rs = nothing
    exit function
    End if

    rs.movefirst
    Qty = rs!Quantity
    do while not rs.eof
    For X = 1 to Qty
    call FunctionToAppend(rs!FirstName, rs!LastName, rs!Address) <= this is the function which opens/appends to the temp table, passing the fields to the function to populate the table.
    next X
    Qty = rs!Quantity
    rs.movenext
    loop
    rs.close
    set rs = nothing

    .....open report here.
    End Function
    Last edited by pkstormy; 11-06-09 at 21:37.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    there is a much simpler solution alternative; if you have a single record (the source for the label) that you want repeated X times: Make a table called Numbers that is a single field...and add as many records as numbers you will need. i.e. if you will need 50 labels your table will have 50 records 1-50.

    Create a new query that has two objects(tables/queries); one is the one that has your label text/barcode ...this should be a record set of just one record. and your second object is this new Numbers table. Put all the fields into the query. Don't make a join. When you run the query you will have that one record repeated as many times as there are numbers.

    If you need a query result with less than 50 records sometimes i.e. 20 - you can dynamically put in <21 as a criteria in the numbers field of the query....

  5. #5
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Wink Repeated lables

    HI NTC (and all others)

    Your function is OK if you want the same number of each label in the input table.
    But my table holds approximate 500 records before printing the labels.
    Based upon a number field in the table: record 1 should have two copies, record 2 seven copies, record 3 nine copies …
    Totally this could typically generate 1500 labels. Some records single labels other records multiple.
    My solution is certainly not the most sophisticated, but it function 100&#37;

    … and “pkstormy’
    Yes, we have the same solution (two loops) - but with different approach. :-)
    [thread]
    http://www.dbforums.com/microsoft-ac...ml#post6431900

    Sincerely
    Vestigo
    Last edited by vestigo; 11-06-09 at 19:50.

Posting Permissions

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