How to add and Subtract a quantity from form table

[pokeytrev]

I am wondering how I can go about entering a number in a form and having it add that number to a sum in another table.

I have an inventory database that keeps track of current inventory items in tblInventoryItems and a table that keep track of transactions called TblTransactionHistory. There is quite a bit of data, so I don't want to include everything in one table where it needs to calculate levels all the time.

I want to be able to enter a number into the Transaction form--linked to transaction history--and have it update the transaction history table with a line in the table and update the inventory quantity in the main inventory table.

Is there a group of lines I can enter into code builder to accomplish this?

 

[sneuberg]

You could create a update query using the query builder. Something like

• Add the table to be updated to the query
• Click Update in the ribbon
• Below to the right of Table: pick the table to be updated
• Just above that pick the Field to be updated
• Right click in the field next to Update to: and click Build in the menu
• In the expression builder, build the expression you need you will want to build something like Forms!formnamewithtextbox!textboxname + tablenameoftable!nameoffieldintable
• In the Criteria field you can build the appropriate criteria (WHERE clause) Which record is to be update? Without this all records will be updated
• Save the query
• When you add a button to your form you if you choose Miscellaneous in the Categories given by the wizard you will see run query as on option. This will allow you to run the query you created.

 

[TJPoorman]

Assuming your Transaction History is append only you could add the following to the "AfterInsert" event of your form:

CurrentDb.Execute "UPDATE tblInventoryItems SET CurrentQuantity = [CurrentQuantity] + (" & Me.TransactionQuantity & ") WHERE ItemID = '" & Me.ItemID & "'"

If your Transaction History form has edit ability you would need to add some different code in the AfterUpdate event.

The code above is assuming 2 things:
1. Your transaction quantity will be in the form of negative numbers if removing and positive numbers if adding.
2. Your ItemID is a string field. If ItemID is a number just remove the single quotes.

 

[pokeytrev]

This is great! How would I change the table to append only?

 

[gemma-the-husky]

are keeping an up to date balance of the quantity on hand at present?

that isn't the recommended procedure.

the recommended procedure is not to store the balance, but to sum the transactions making up the balance.

 

[pokeytrev]

are keeping an up to date balance of the quantity on hand at present?

that isn't the recommended procedure.

the recommended procedure is not to store the balance, but to sum the transactions making up the balance.

I am completely fine with using the recommended procedure, I just need to know how to make the calculations.

 

[jdraw]

You have a table for transactions.

When you receive goods , these are Positive transaction amounts.
When you sell good, these are negative transaction amounts.

So from some point in time (after a physical inventory count)

current GoodsInventory = Count at some point + GoodsReceived - GoodsSold

see this Allen Browne article for more details

 

[MarkK]

There may be some overlap in this thread and this one: http://www.access-programmers.co.uk/forums/showthread.php?t=284854

Cheers,

 

[gemma-the-husky]

Ok. I see.

I would add the latest balance to the transactions table with the date, and maybe store this date in the product table also

this sort of thing.
currentdb.execute "update producttable set stocktakedate = whatever"

then the balance on hand needs only sum transactions from the relevant date in the transactions table.

if you are desperate to store a moving on hand balance then the same idea can be used.

currentdb.execute "update producttable set currentquantityonhand = whatever"

I wouldn't do this - you have to add it in to any process that affects the balance on hand. A lot of work, and easy to miss some processes.