Subtract from inventory

[NISMOJim]

I have a table (tblInventory) with a quantity field (Qty). I am trying to subtract the quantity that the user enters on the transaction form (frmXaction) as removing from inventory, from the quantity in the inventory table. When that is done, I want the transaction form to close, then the Inventory detailed form (frmInventoryDetail) to close, and then requery the inventory (frmInventory) to show the new quantity in the inventory. I copied the code from another post & put in my names. I must have missed something, because now I get a runtime error 3144: Syntax error in update statement. It looks just like the code as far as I can see. Does anyone else see a problem with this code doing what I want it to do?



Private Sub CmdConfirm_Click()
Dim var As Long
var = DLookup("Qty", "tblInventory", "[Indx] = " & Me.txIndx) - Me.Qty

CurrentDb.Execute "UPDATE table SET " & _
"tblInventory.Qty = " & var & " WHERE " & _
"[Indx] = " & Me.txIndx
DoCmd.Close
DoCmd.Close
DoCmd.Requery

End Sub

[pbaldy]

You refer to a table named "table" and then "tblInventory".

[NISMOJim]

I have so much to learn. Copying code should be easier than I'm making it. That part is working fine now. I still need to add more code before closing the forms about deleting the record from the table if the new value = 0;

If "tblInventory.Qty" = "0" then
DoCmd.DeleteRecord
End If

Should this do the trick?

Thanks for all your help so far, it seems you never sleep!

On second thought, of course that won't work. I will need to refer to the same index number that is on the open form.
Something like;
DoCmd.GoToRecord tblInventory Where "Indx = " & Me.Indx
If "tblInventory.Qty" = "0" then
DoCmd.DeleteRecord
End If

Is that any closer?

[pbaldy]

Trust me, I get my sleep; it's 11pm now, and I'm off to bed.

You don't need to go to a record, as you'd already be on it, no? You could use another DLookup to check the value, but your variable var would already give you that answer, wouldn't it? And would you really want to delete the item? You wouldn't buy more of it?

I'll say that I'm not a fan of trying to keep a quantity field updated in general. In the inventory applications I've written, I just calculate quantity on hand from transactions (quantity in less quantity out). There's a nice discussion here on the subject:

http://allenbrowne.com/AppInventory.html

[NISMOJim]

I've also seen it written in these posts that it's not a good idea to update the quantity field. In actuality though, when an item (tires in the case of this db) is depleted, new tires that come in to take their place would be a seperate record due to the tracking of a DOT number. When the quantity of a batch of tires reaches 0, that record is never refered to again, thus taking up space in the table unless deleted.

I've been trying different things to get the above code to work, but keep getting a compile error-method or data member not found. I'll be away from this project for a few days, which may give me some time to read up on it, but even though you don't agree with the method, any direction with the syntax would be appreciated. I'll also look into Mr. Browne's link (a real genious! He's helped me a few times in the past on the old forum).
Thanks again.

[pbaldy]

I did try to help with the method. Like I said, at the point you populate your "var" variable, you know if it's zero or not. Test for zero, and either execute your update query or a delete query to delete the record.