The first reference to the field in the subform lacks the ".Form" bit. A2003
is more finicky about that than A2000 is. Try:
UPDATE tblOrder SET tblOrder.UnshippedQty = [UnshippedQty] -
[forms]![frmBillofLading]![frmBillofLadingItems subform].Form![ItemQty]
WHERE ...
This highlights another problem with the approach. If you have turned off
SetWarnings, you receive no indication that the query failed to execute.
That's completely unacceptable if you are relying on it to give you correct
quantities. A better solution would be to use the Execute method instead of
RunSQL. It's a little more involved, because you must concatentate the
values from the form into the string. Something like this:
Dim strSql As String
strSql = "UPDATE tblOrder SET tblOrder.UnshippedQty = [UnshippedQty] - " & _
Forms!frmBillofLading![frmBillofLadingItems subform].Form!ItemQty & _
" WHERE tblOrderID = " & _
Forms!frmBillofLading![frmBillofLadingItems subform].Form!ItemQty & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError
Of course, there is an even more fundamental problem, about what event you
use to execute this. If you use the AfterUpdate event of the form, have you
considered what would happen if this is not a new record? For example, if
the user changed the ItemQty from 100 to 10, do you restore the other 90
somehow? Or if they changed the product, do you restore the quantity of the
other product as well as subtracting from this one? And what if they delete
the entry: do you restore the deleted quantities for each product? Even if
multiple rows are selected and deleted at once?
You might like to consider calculating the quantity when you need it instead
of jumping through hoops to try to keep it up to date, and still having
failed query writes like the one in your question messing it up. For an
alternative approach, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Mary Fran said:
I have Access 2000. Another user has A2003. We have the same database.
The
code to reduce unshipped items is
DoCmd.RunSQL "UPDATE tblOrder SET tblOrder.UnshippedQty =
[UnshippedQty]-[forms]![frmBillofLading]![frmBillofLadingItems
subform]![ItemQty] WHERE
(((tblOrder.ID)=[forms]![frmBillofLading]![frmBillofLadingItems
subform].[form]![ItemID]));"
If all items ship ([UnshippedQty]=[ItemQty]) my UnshippedQty results in 0.
The A2003 user's field shows null.
Why would that be?
