Results 1 to 3 of 3
  1. #1
    beachbumch is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    5

    Printing labels from order data in Access ERP

    I have worked through printing labels in our Access based ERP system and it works 80% of the time. I can print one label for each product line item based on the QTY ordered. I ran into a problem when QTY 2 or more have different lot numbers.

    For example:

    PRODUCT CODE = 12345
    QTY = 2
    LOT = XYZ

    PRODUCT CODE = 54321
    QTY = 1
    LOT = ABC

    This prints correctly. This results in 3 labels printing. (QTY X product code works in both cases. This is because there is only one lot number per product code)


    PRODUCT CODE = 12345
    QTY = 2
    LOT = XYZ
    LOT = ZYX

    PRODUCT CODE = 54321
    QTY = 1
    LOT = CBA

    This is where it fails. This results in 5 labels printing when it should be 3. (2 X Each lot number/product code, + 1 X the single product code/lot number)

    I use a count table in a query of our order tables to generate this label data.


    SELECT ORDHFILE.BL, ORDTFILE.QORD, ORDTFILE.PNUM, ORDTLOT.LOTNUM


    FROM tblCount, ORDTLOT, ORDHFILE INNER JOIN ORDTFILE ON ORDHFILE.BL = ORDTFILE.BL
    WHERE (((ORDHFILE.BL)=[Enter Order Number:]) AND ((tblCount.CountID)<=[QORD]));

    (BL in the above is actually an order number)



    Would someone please give me a little guidance here?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It sounds that your real criteria is ProductCode + LotNumber.

    Using you data example:
    PRODUCT CODE = 12345
    QTY = 2
    LOT = XYZ
    LOT = ZYX
    Code:
      would give ProductCode    Lot   Qty
                           12345       XYZ   1
                           12345       ZYX   1
    2 labels

    PRODUCT CODE = 54321
    QTY = 1
    LOT = CBA

    Code:
       would give  Product code   LOT  Qty
                             54321       CBA   1
    1 label

    Hope this is helpful

  3. #3
    beachbumch is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    5
    Thank you very much for the reply.

    To be clear, the QTY is how many of one code was ordered. So the customer ordered 2 of product code 54321.
    I use a count table to break QTY for each product code into line items so they can be printed. When the customer purchased 2 of product code 54321, it was one line item and therefor printing one label even though they purchased 2. The count table added to my order query looks at the QTY of an order and creates a line item for each product code allowing 2 labels to be printed. This all works great

    The problem occurs when we use multiple lots to fill the order for one product code. We have an order table, lot table, and my count table to work with. We look to the order table for the QTY ordered to specify how many line items should be created for label printing. The order table data has one line item per product code ordered. The lot table contains a line item for each lot used per product. So if customer purchases 2 of product code 54321 and we used 2 lots to fill the order, the order tables will have one line item for the product code and the lot file will have 2.
    i.e.

    (Order Table)
    Product Code = 54321, QTY 2

    (Lot Table)
    Product Code =54321, Lot 1234
    Product Code =54321, Lot 4321

    The result of above will be 4 labels. (QTY 2 X the the number of line items in the lot file)

    Thanks again for the help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reset to default printer after printing labels
    By chromachem in forum Programming
    Replies: 6
    Last Post: 02-26-2016, 11:23 AM
  2. Code for Printing Labels
    By bezoomnyveshch in forum Access
    Replies: 1
    Last Post: 12-15-2015, 01:21 PM
  3. Replies: 6
    Last Post: 07-28-2014, 01:04 PM
  4. Trouble printing labels (data from Access) in Word
    By Austruck in forum Import/Export Data
    Replies: 2
    Last Post: 08-08-2011, 10:23 AM
  5. Printing Labels.
    By Robeen in forum Reports
    Replies: 1
    Last Post: 05-02-2011, 05:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums