Updating single cell in table from a form 1

[Simon22]

Hi all,
I would like to make a simple transaction database. Inventory and orders. When an order is processed, the related inventory goes down by the order amount. However, I cannot figure out how to get my order form to update only one cell rather than the entire atribute column. I was told yesterday that I should not have a calculated field in a table and that the inventory count should come from a sum of all the transactions, but isn't that a bit inefficient making the database add up every single transaction each time to find the inventory on-hand? I want to have an atribute in the product table for inventory. Can anyone tell me how to get my form to update the item's on-hand field?

Ex.

product table

a) Widget 25
b) Gidget 10
c) Thing 27


1) 25 widgets on-hand
2) Order Form: customer buys 1 widget
3) widgets on-hand now reads 24

How do I do this???

Thank you very much for your help

 

[tlbroadbent]

You should be able to create and execute an update query in VBA. The following assumes you have an column named InvQty on the Product table as well as a ProdID column. It also assumes that you have a form which contains the order information including the ID txt (ProdID) of the product ordered and the quantity ordered (txtQtyOrdered). The code would be executed when the Order Form is updated.

Dim strSQL As String
strSQL="Update Product Set InvQty = InvQty - " & _
Me.txtOrderQty & " Where ProdID = " & txtProdID

DoCmd.RunSQL strSQL Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Simon22]

Hi Terry!

Thank you for the help. I am having one problem though. I put the code in the AfterUpdate of the form and I am getting a compile error. It says "method or data member not found" and under lines "Me.txtOrderQty" (in my case, the OrderQty reads Order_Qty) Is it the underscore that is causing the problem?

Thanks

 

[tlbroadbent]

The underscore shouldn't be a problem. Post your code and the names of the form and objects on form. It would be good to see the table column names also.

Thanks, Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Simon22]

Here is my database info

Tables:

Product(Prod_ID, Prod_Desc, Prod_Inv)
Order(Order_ID, Order_Date, Order_Amt, Prod_ID)

Form: New Order

Txtbox: Order_ID
Txtbox: Order_Amt
Txtbox: Order_Date
Txtbox: Prod_ID

Here is the code I have in there:

Dim strSQL As String
strSQL="Update Product Set Prod_Inv = Prod_Inv - " & _
Me.txtOrder_Amt & " Where Prod_ID = " & txtProd_ID
DoCmd.RunSQL strSQL

I don't know anything about VB so this is alien to me. A few things I noticed though? Should there be a closing quotation mark after Where Prod_ID = " & txtProd_ID (The quotations seem to be in strange places)?
Also, did I put this code in the right place? I used build code in the After Update of the Form properties. Is there a way that this can be done without VB? I would like to be able to replicate it but, like I said, I don't know anything about VB.

 

[tlbroadbent]

I beleive the code is in the correct module. There are other ways to handle this sort of update but this is about as simple as it gets.

It appears you need just a minor adjustment in the code. The text box names you posted do not have a "txt" prefix as in my example. Simply remove the prefix in the VB code where the SQL string is created.

strSQL="Update Product Set Prod_Inv = Prod_Inv - " & _
Me.Order_Amt & " Where Prod_ID = " & Me.Prod_ID

The quotations are correctly placed around literal strings. The text boxes are outside the quotes so the value of the text boxes can be evaluated. You want the amount to become part of the query string - not the name, "Me.Order_Amt." Likewise with the Prod_ID text box. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Simon22]

Hi Terry,
It isn't causing errors anymore but there is a slight problem. When I run it, Access pops up a form to enter a parameter for Prod_ID even though I have a textbox called Prod_ID and a value in it. But, when I enter the correct Prod_ID in the parameter box that pops up, the whole thing works perfectly. Any ideas?

 

[tlbroadbent]

Will you post the code once more please? I'd like to see the entire After Update module. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Simon22]

Here is the complete module:

Option Compare Database


Private Sub Form_AfterUpdate()

Dim strSQL As String
strSQL = "Update Products Set Prod_Inv = Prod_Inv - " & _
Me.Order_Amt & " Where Prod_ID = " & Me.Prod_ID
DoCmd.RunSQL strSQL

End Sub

 

[tlbroadbent]

Is Prod_ID numeric or text? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Simon22]

Prod_ID is text TX10, TX20, TX30

 

[tlbroadbent]

I don't understand what you mean by TX10, TX20, TX30. Are those values of the Prod_ID?

You need to enclose the value from the form in quote if Prod_ID is data type text. Use single quotes (').

strSQL = "Update Products Set Prod_Inv = Prod_Inv - " & _
Me.Order_Amt & " Where Prod_ID = '" & Me.Prod_ID & "'" Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Simon22]

Yes, those were examples of the Prod_ID's just in case it would help.

I tried the single quotations but I get an error:

Syntax error (missing operator) in query expression 'Prod_ID ='

Here is the code as I it is now:

Option Compare Database


Private Sub Form_AfterUpdate()

Dim strSQL As String
strSQL = "Update Products Set Prod_Inv = Prod_Inv + " & _
Me.Order_Amt & " Where Prod_ID = " '& Me.Prod_ID'
DoCmd.RunSQL strSQL

End Sub


I'm confused as to the quotation marks. You said single quotes but there are a bunch of different quotation marks in the BOLD text of your last post. Did I interpret it incorrectly?

 

[tlbroadbent]

You didn't use the code I provided. The single quotes are part of the literal strings that must be enclosed in the double quotes. Please look at my example again. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Simon22]

Perfect!! I didn't copy/paste the first time and I must have mis-interpreted the quotation order. This time I copy/pasted and it worked perfectly. My thanks to you for your time and assistance

 

[tlbroadbent]

You are welcome! Enjoy the day. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.