Inventory: add/remove stock qty from table

[crispyc]

I hope someone can help me - I haven't touched Access in years and I have been "volunteered" by my employer to write a basic inventory system. I am using Pendragon forms along with the now obsolete Handspring Visor equipped with scanner to scan barcodes and collect data. This I am okay with. What I can't seem to wrap my head around is performing a record lookup from the scanned data (I have a table with scanned data to add to inventory (tblAddInventory), a table with scanned data to remove from inventory (tblRemoveInventory) and a master table with all of my warehouse inventory (tblWarehouseMain) and performing a calculation on the records to add - or subtract - stock, depending on what table the data comes from. I am (er, rather used to be) comfortable with VB. I have tried to create an update query as well as some basic code via control on a form, but I'm finding myself at a loss. Anyone willing to take a shot at helping or pointing me in the right direction? I'd sure appreciate it. Thanks!

 

[DCrake]

Presumably each stock item has a quantity in Stock field, just run an update query to deduct the amount out from the current total.

Update Table Set Qty = Qty-AmountOut Where Id = x

 

[crispyc]

Presumably each stock item has a quantity in Stock field, just run an update query to deduct the amount out from the current total.

Update Table Set Qty = Qty-AmountOut Where Id = x

Thank you! I knew I was trying to make this more difficult than it needed to be. I'll try that now!

 

[crispyc]

please excuse my ignorance ...this is what I have tried in my query:

UPDATE tblWarehouseMain
SET Quantity = (tblWarehouseMain.Quantity + tblAddTotals.qtyADD)
WHERE Barcode = tblAddTotals.Barcode

When run, this returns "Enter Parameter Value" "tblAddTotals.qtyADD" OK/Cancel.

I'm running Access 2000.
Both the tblWarehouseMain.Quantity and tblAddTotals.qtyADD are number fields

Thank you for any help/advice. C

 

[DCrake]

This is becuase you have not included your table into the query and joined the two tables via the barcode field.

 

[crispyc]

David, I can't thank you enough. That worked beautifully. I can't believe how rusty my mind is!

Thank you very much for the assistance.
C

 

[traceya]

This is becuase you have not included your table into the query and joined the two tables via the barcode field.

Could someone please quote the correct syntax that should have been used here as I'm trying to figure out a similar problem myself and don't have much Access knowledge.
Thanks

 

[sweatshopking]

I have a similar problem. I'm a total noob, and don't know how to use code, but i'm working with access 2013 and trying to help my brother out with his business. I'm trying to figure out how i can remove stock when a sale is made. It's for a photography business, so i need to be able to remove specific kinds of paper from inventory when a specific printing package is selected. I have a table of inventory, which includes stock numbers, and i need to figure out how to add and then remove inventory as these package skus are used.

I was planning on using a form for sales, and then moving the form data to another table so that COGS could be maintained in a separate form to track cost pricing differences over time for tax purposes. TBH not sure how i'll accomplish this yet, but I haven't gotten to that step of the problem yet, so i'm not worrying about it. My knowledge of access is 2 semesters of it in community college, but we don't touch on VBA at all, and I haven't touched it since 2002 in high school. I'm not even sure where you'd add it.

Is anyone able to provide me any guidance or advice on how to add or remove stock from a table with my knowledge level? i've been watching youtube, but can't seem to get a handle on it.

I appreciate your time and effort!