I have a form that I have been using to enter info into a table. I have this form setup so that it automatically goes to a new record so the user can start entering data. Form: frmMaterialIn Table: tblMaterialInvoice
This is where I enter all material invoices. I have another table that tracks raw material. Table: tblRawMaterial
I would like to be able to update the tblRawMaterial as new invoices are entered into tblMaterialInvoice with frmMaterialIn.
I am appending a new record into tblMaterialInvoice with each entry.
And finding the correct record in tblRawMaterial with a common field.
The common field in both tables, is IncomingMaterialNum (Text Field).
The field that I am updating in tblRawMaterial is Amount.
I have been using the UPDATE statement to try to do this. I can get the statement to work as long as I use numbers but when I try to use the name of the form text box in the SQL statement I get an error message.
I made a Command Button is test my calculation.
The Code below works.
Private Sub cmdAddRaw_Click()
Dim dbs As DAO.Database
Set dbs = CurrentDb
' Change values in the Amount field to Amount = (Amount = Amount + txtAmount)
dbs.Execute "UPDATE tblRawMaterial " _
& "SET Amount = Amount + 2222 " _
& "WHERE IncomingMaterialNum = '2437';"
End Sub
But, when I replace the '& Set' line of code to this,
& "SET Amount = Amount + me.txtAmount " _
I get an error code.
txtAmount is the name of the form text box where the invoice amount is entered.
Also, when I get the amount to work I will change the '2437' to the actual text box name for Incmoing Material Number; txtMaterialNum.
Any help would be greatly appreciated.
Ksmith