Hi all, I have an inventory table

[cassandra]

Hi all,
I have an inventory table which has the following fields:
Date; Code; Beginning_inventory; Qty received; qtyShipped; then I have a query which calculates the ending inventory. It follows that today's ending inventory for a particular code will be tomorrow's beginning inventory. My problem is I can't figure out how to automate this process, so that when the user adds new records, yesterday's ending invetory is used as today's beginning inventory. I hope this makes sense.

Thanks all

 

[DougP]

Well you need a trigger point. such as opening the database or clicking a "new record" button.

Say you are working with part number 1234 its ending QTY was 55 so when you click "Add new Record" it looks at the system date and gets a date which is "one" minus today. If today is a Monday then it needs to get Fridays Date or subtract 3(tricky when you have Holidays) anyway.
Get the Qty and save it in a variable like: yesterdayQTY.
then Add a new record like so
DoCmd.GoToRecord , , acNewRec

then make the QTY of the new record = yesterdayQTY.
then save the record like this:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive