Ms Access SQL not updating when form is open
Posted on 2014-01-02
I have form that prints an Invoice. When the invoice is printed it updates the inventory table to adjust for the number of items sold. I do this with the following statement.
DoCmd.RunSQL "UPDATE tblInvoiceDetail INNER JOIN tblInventoryData ON " _
& "tblInvoiceDetail.Partno = tblInventoryData.Partno SET " _
& " tblInventoryData.QuanInStock = [tblInventoryData].[QuanInStock]-[tblInvoiceDetail].[OrderQuan] " _
& "WHERE (((tblInvoiceDetail.[InvNo])=" & InvNo & ") AND ((tblInventoryData.DropShip)<>-1));"
This works perfectly but if my Inventory form is also open to one fo the items being updated, the update for that item does not occur. What can I do to fix this?