Update query in VBA (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 04:57
Joined
Dec 8, 2007
Messages
163
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:

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
 

Mr_Si

Registered User.
Local time
Today, 04:57
Joined
Dec 8, 2007
Messages
163
Update, I have just worked out that I needed to use CurrentDb.Execute instead of Db.Execute.

Sorry to bother you.
 

Users who are viewing this thread

Top Bottom