VBA RunSQL to update existing field record in table from Form (1 Viewer)

[daps07]

I am new to Access VBA, slowly building up knowledge, and having trouble getting an On Click Update Event using RunSQL to work.
I have a form bound to the Card Description table1 to get the Onhand_Stock value, as long the Product name meets then it will get the value from the Vault_Stock location.
This transaction works fine.

From the form I created.
Current_Stock = Onhand_Stock - Transaction_QTY

Once the Current_Stock is already updated from the form transaction, I need to get back to the Card Description table1 and update the existing record from the result value I have on the Current_Stock. But the problem is I have some difficulties to updates the existing record (On-hand) right after current_stock is updated.

Based on my understanding I need to use DoCmd.RunSQL UPDATE after right below condition
If cboStockLocation = "VAULT_STOCK" Then ???

But I don't where to start.
Any help appreciated.

 

[CJ_London]

You will be constantly told not to store the current stock value, calculate it as and when required. So where to start is to not do it.

reason is simple, real life means you will have occasions where the quantity is wrong, the date of the transaction predates an existing transaction, to name two reasons.

describe your situation - what tables do you have, what types of transactions, where this information comes from

in principle you just have one transaction table plus perhaps a transaction header, products, transaction type table, maybe customers and suppliers

 

[June7]

Review http://allenbrowne.com/AppInventory.html

 

[daps07]

The concept of the Database actually is to create Inventory for Stock In and Stock Out wherein I have 2 Stock locations Vault and cabinet.
I have a Product name table that includes the onhand stock for both Vault and Cabinet.

From the form, I have a Combobox combination where I need to select the following. Stock Location, Transaction Type, Customer name, Product name, Textbox (onhand) (where the value is Dlookup on the Product name table) depending on the combination on the combo box, Transaction QTY (for the QTY that I need to process) and Current Stock (textbox) for the result.

Secondly, I have an inventory table history wherein it records every transaction made from the form.

But this table is useless if I can't update the Product name table that includes the onhand stock for both Vault and Cabinet
My only problem is how would update the records from the stock location right after the transaction is made.

 

[June7]

And we are saying you don't update, you calculate. So why would you think transaction table is useless? Calculation would be based on transactions. But yes, if you really must, then run an UPDATE action.

CurrentDb.Execute "UPDATE tablename SET fieldname = fieldname + " & Me.textboxname & " WHERE ID = " & Me.ID

Then wait until the day you realize the saved values don't agree with the actual numbers because something went wrong with the input.

 

[The_Doc_Man]

Again, the correct way is not to store the balance anywhere, but instead to recognize that the balance is the sum of all related transactions. The solution for vault/cabinet is that you include the location in the transaction and then can separate the results for each.

The moment that you enter a completed transaction, you can run a sum of all transactions and the count will be correct. Your transaction history IS the inventory if you do it right.

 

[plog]

But this table is useless if I can't update the Product name table that includes the onhand stock for both Vault and Cabinet

Good we all agree.

You're so focused on your improper solution you aren't taking a step back to actually listen to the correct solution others are providing because its not furthering you in your process towards your solution.

You do not store the balance after every transaction. You calculate the balance when you need it--even if that means calcualting it for every record. Others have provided more specific solutions, so I won't. I just want to add my voice to literally everyone else who says your solution is improper and are providing a good one.

 

[gemma-the-husky]

It's not actually slower doing a quick sum every time you need the current balance. Access is super fast at sums.

It's far easier than trying to maintain a balance, and then managing adjusting the balance every time you amend, or delete a transaction or even insert a new transaction. What if you need to enter forward dated transactions (I don't know whether that's possible). How do you manage those?

 

[Solo712]

The concept of the Database actually is to create Inventory for Stock In and Stock Out wherein I have 2 Stock locations Vault and cabinet.
I have a Product name table that includes the onhand stock for both Vault and Cabinet.

From the form, I have a Combobox combination where I need to select the following. Stock Location, Transaction Type, Customer name, Product name, Textbox (onhand) (where the value is Dlookup on the Product name table) depending on the combination on the combo box, Transaction QTY (for the QTY that I need to process) and Current Stock (textbox) for the result.

Secondly, I have an inventory table history wherein it records every transaction made from the form.

But this table is useless if I can't update the Product name table that includes the onhand stock for both Vault and Cabinet
My only problem is how would update the records from the stock location right after the transaction is made.

IIUC, to follow up the quantities of the products in two locations after each transaction then your "history table" has to indicate which location the transaction relates to. Then you can calculate the new balance of the product, in the Vault and the Cabinet separately by creating a field for it. Again, you do not need to update the product table to reflect the current on-hand inventory in the two locations. You simply need a field in the inventory transaction table labeled "Location", to track the products in the Vault and the Cabinet.

Best,
Jiri