Inventory pallet control?

[rzkl]

I am try to make an access database to create pallet tags for some package and ingredients items that we purchase.
What I would like to do is after the purchaser creates a PO in our purchasing system he would enter that information (SKU and total quantity)into a table. Someone from the warehouse would then go into this database, pull up the PO number and at that time Im guessing a query would be run on those original lines. This is where I know what I want access to do but don’t know how to get there.
Assuming that we ordered 1000 of an item, Access would look at a table with the per pallet quantities and then create the appropriate amount of pallet tags and create a line in a table with the qty and the pallet number. I would then go back at a later date and enter a lot number to go with that pallet number.

Any Ideas? I have searched and have not found anything on this.

Thanks

 

[DALIEN51]

Try This!

OK so you have a order table...Purchase Order Number, Quantity.
Create a label table and purchase order number selection form possibly with a combo box listing all purchase order numbers then put this code behine a print labels button on that form.

DIM MYDB AS DATABASE
DIM SOURCE AS RECORDSET
DIM TARGET AS RECORDSET
DIM CT AS LONG

SET MYDB = CURRENTDB()
SET SOURCE = MYDB.OPENRECORDSET("{insert name of order table}")
SET TARGET = MYDB.OPENRECORDSET("{insert name of label table}")
CT = 0

DOCMD.SETWARNINGS FALSE
DOCMD.OPENQUERY("{create a reset labels table query}]

SOURCE.MOVEFIRST
DO UNTIL SOURCE![{purchase order number field}] = FORMS![{form name}]![{field name on form}]

SOURCE.MOVENEXT
LOOP

DO UNTIL CT > SOURCE![{quantity of pallets field}]
CT = CT + 1
TARGET.ADDNEW
TARGET![{purchase order number field}] = SOURCE![{purchase order number field}]
TARGET![{pallet number field}] = CT
TARGET.UPDATE
LOOP

Then call a report you have created to print all the records in the label table!

Simple! (ish)