Update table when insert on another table

Question

So I have two tables: Product and Inventory.

The product table has the following columns: ProdId - ProdName - Price - StockLevel.

The inventory table has the following: InvId - ProdId - RestockDate - RestockQty

I have used openargs on a button (Add to stock) placed on the product entry form, that passes the ProdId value to the inventory entry form.

What I need now is to increase the StockLevel value in the product table by the value specified on the inventory form.

I have the following vba code placed on the oncurrent event of the inventory table:

     IF Me.NewRecord = True Then
        Dim db as database 
        Dim MySQL as string 

        Set db = CurrentDb 
        Mysql = "Update Product Set StockLevel = RestockQty+StockLevel where ProdId =" & Me.ProdId & ""

Db.Execute Mysql, dbfailOnError
End If

But I get the following error: Run-time error '3061:' Too few parameters. Expected 1.

What am I doing wrong?

Gustav Gustav · Accepted Answer · 2017-02-06T17:09:53

Try with:

Mysql = "Update Product Set StockLevel = StockLevel + " & Nz(RestockQty, 0) & " where ProdId =" & Me.ProdId & ""

Update table when insert on another table

1 Answers