OnClick Event procedure on "Update Record"

[spdolan]

Hey folks -

I have a db Im working on in which I need to use one button to update two tables. I have one table that shows data recently entered, and another table which will search for and update quantities if SKU exists, or append the record to the table if it does not, when Update record is clicked. For example, product A is entered with a quantity of 5. Update record is clicked. Snapshot table shows that A has quantity of 5. Meanwhile Inventory table now shows that A has total quantity of X+5, where X was the Total quantity before Update Record button was clicked.

Is this possible, or am I tilting at windmills?

Sean

 

[pbaldy]

As a friend likes to say, anything is possible with enough time and money. That said, it's usually not a good idea to try and store quantity on hand. Some thoughts here:

http://allenbrowne.com/AppInventory.html

 

[spdolan]

Yes, I know I'm not supposed to store quantity on hand, but in my case, I am dealing with an alphabetized warehouse of books, stacked on shelves, and we dont know what books we are going to have. I have a table which is essentially a pool of potential inventory, and a table which is actual.

I think I may be in the minority in which inv on hand is necessary to store, because while inv will be entered in multiple quantities, it will be sold off book by book.

I am fairly good at SQL queries, and slightly familiar with VBA code. What I would like to do is code the button's onClick proc to accomplish my goals.

Thanks

Sean

 

[pbaldy]

Not sure that's a good reason, but among the methods:

CurrentDb.Execute "UPDATE TableName SET Quantity = Quantity + " & Me.Quantity & " WHERE ProductID = " & Me.ProductID

You could also use a recordset.

 

[boblarson]

I think I may be in the minority in which inv on hand is necessary to store, because while inv will be entered in multiple quantities, it will be sold off book by book.

That shouldn't matter. All you need is to use a method like Accounting does - Debits and Credits and you add them up to get the total. Incoming or outgoing it matters little how it gets there but if you are tracking via title then it needs to go in via title.