Hi there,
It's been a while.
I've never used an Update query before, but I have a perfect requirement for one now.
I have a form (frmOrders) with a subform (frmProduct_OrderAmount) in it.
Linked by the Order number.
When I do a new order, I enter the products for that order by use of the subform and then the quantity of each product.
I want to be able to update the "StockLevel" field in a table (tblProduct) based on the quantity entered in the subform.
I have the following code:
Now, this, when run gives me an error saving that an object is required, but I cannot understand why.
So I would like some guidance if at all possible, please.
Many thanks,
Simon
It's been a while.
I've never used an Update query before, but I have a perfect requirement for one now.
I have a form (frmOrders) with a subform (frmProduct_OrderAmount) in it.
Linked by the Order number.
When I do a new order, I enter the products for that order by use of the subform and then the quantity of each product.
I want to be able to update the "StockLevel" field in a table (tblProduct) based on the quantity entered in the subform.
I have the following code:
Code:
Private Sub UpdateStock_Click()
Dim strUpdate As String
Dim strWhere As String
Dim strSQL As String
strUpdate = "UPDATE tblProduct SET ProductStockLevel = [tblProduct].[ProductStockLevel]-" & Me.Quantity
strWhere = " WHERE idsProductID = " & Me.ProductID
strSQL = strUpdate & strWhere
MsgBox strSQL
If Me.UpdateStock.Value = True Then
If Form_frmOrders.Confirmed.Value = True Then
'run update query to decrement value of product stock by value of me.quantity
db.Execute (strSQL)
MsgBox Me.Quantity & " of this product removed from stock."
'disable tick box to not be used again
Me.UpdateStock.Enabled = False
End If
Else
'keep tickbox enabled
Me.UpdateStock.Enabled = True
End If
End Sub
Now, this, when run gives me an error saving that an object is required, but I cannot understand why.
So I would like some guidance if at all possible, please.
Many thanks,
Simon