procedure to change inventory from the invoice qty

[Ryno Bower]

Hello everybody,
I want to know if anybody can help me. What procedure or code can be used if I want to do the following.
I have a invoice entry form and a inventory/stock form showing stock and qty.
If a qty is entered on the invoice form and I click a button "print invoice" I want the qty that is on the invoice to be subtracted from the qty in stock.
I have tried many on click events to minus the one from the other but didn't work out properly.
If you want me to give you details of what tables I have etc, please ask. But for now I just want an explanation of what to do where so that a procedure can handle this for me.

Thanking you.
Ryno

[MikeTheBike]

Hi

I don't know what youe level of expertise is with queries/SQL, but you could create an stored action query linked to this form referring to the Invoiced quantity (ie an Update query), to update the 'Stock' table after the invoice has printed successfully.

ie DoCmd.RunSQL "StoredQueryName"

You could also create the query in code something like this

Dim SQL As String
 
    SQL = "UPDATE StockTableName " & _
          "SET StockTableName.Quantity = StockTableName.Quantity -" & Me.InvoiceQuantityControl.Value & " " & _
          "WHERE StockTableName.ID = " & Me.ID
 
    DoCmd.RunSQL SQL

MTB

[Ryno Bower]

Hello

Thanks for your reply. I used the following code, but when I click the button, it wants me to enter a parameter value and I dont know why.

This is what I have

Private Sub Command11_Click()
Dim SQL As String
 
    SQL = "UPDATE Inventory " & _
          "SET Inventory.QtyOnHand = Inventory.QtyOnHand -" & Me.ShipQty.Value & " " & _
          "WHERE Inventory.StockCode = " & Me.StockCode
 
    DoCmd.RunSQL SQL
End Sub

[RuralGuy]

Try:

SQL = "UPDATE Inventory " & _ 
            "SET Inventory.QtyOnHand = Inventory.QtyOnHand - " & Me.ShipQty & _ 
            " WHERE Inventory.StockCode = '" & Me.StockCode & "';"

...if StockCode is a String field.

[Ryno Bower]

HI,

Thank you very much, this code works and does not ask for the parameter value. BUT,
Is there a way to work around it so it does not ask me is its ok to update the table? How can i let it just do it without asking to update the table?

Thank you for your reply and help!

[RuralGuy]

Instead of
DoCmd.RunSQL SQL
...use
CurrentDB.Execute SQL, dbFailOnError