Code to UPDATE table (1 Viewer)

[parara]

Hello , i have an open form (sale) , with subform (salesDetails). In subform i have a field (NewQuantity). How i can store the value of field "NewQuantity" , in table "Products" , field "StockRoom1".
Thanks

 

[selevanm]

When you say you are trying to "store" the value "NewQuantity" does that mean you need to insert the value into the database (via INSERT) or is the value already in the Db and you need to update the value?

If you need to do an INSERT here is the syntax I use. I prefer to get the control value in a variable to use in my SQL string but I know you can use the control directly as well.

I am assuming that NewQuantity is an integer data type.

Dim intNewQty as integer
intNewQty = Me.<controlname>.value

strSQLInsert = "INSERT INTO Products(StockRoom1) VALUES (" & intNewQty & ")"
DoCmd.RunSQL strSQLInsert

If you do not want to use a variable but rather the control for the query string I believe the syntax is:

strSQLInsert = "INSERT INTO Products(StockRoom1) VALUES (" & Me.<controlname>.value & ")"
DoCmd.RunSQL strSQLInsert

 

[parara]

Hi, the field 'NewQuantity' is in a subform and has an integer. This value i want to store it in table 'products' , field 'StockRoom1'. I have try with following:

Dim SQL As String
DoCmd.OpenForm "SalesDetails", , , , acFormReadOnly

SQL = " UPDATE Products " & _
" SET Products.StockRoom1 = Forms![Sales]!SalesDetails.Form!NewQuantity " & _
" WHERE Products.IDProduct = Forms![SalesDetails].Form!IDProduct "

DoCmd.RunSQL SQL
Bat nothing , any ideas

 

[selevanm]

Do you receive any errors when the SQL statement executes? Personally I would see if you are getting any values in the following statements by storing the values in variables.

Dim intNewQtyTest as integer
Dim intProdIDTest as integer

intNewQtyTest = Forms![Sales]!SalesDetails.Form!NewQuantity
intProdIDTest = Forms![SalesDetails].Form!IDProduct

This way you can ensure that your SQL statement is getting the expected values from the form and subform.

 

[parara]

Hi again and thanks for your reply.
I try it again and i take message " Ms Access can't found the field name 'SalesDetails'. Bat in my Database 'SalesDetails' is the subform name.

Dim SQL As String
Dim intNewQtyTest as integer
Dim intProdIDTest as integer
intNewQtyTest = Forms![Sales]!SalesDetails.Form!NewQuantity
intProdIDTest = Forms![SalesDetails].Form!IDProduct

strSQLInsert = "INSERT INTO Products(StockRoom1) VALUES (" & intNewQty & ")"
DoCmd.RunSQL strSQLInsert

 

[boblarson]

Hi again and thanks for your reply.
I try it again and i take message " Ms Access can't found the field name 'SalesDetails'. Bat in my Database 'SalesDetails' is the subform name.

Make sure your subform CONTAINER (the control that houses the subform on the main form) is the same name (SalesDetails) because that is the name you need to reference, not the subform name.

See here for an explanation about that:

http://www.btabdevelopment.com/main/LinkClick.aspx?link=76&tabid=55&mid=385