Hope this helps some of you with that quantity and quantity on hand issue. I'm using ACC2000 and I've been reading a lot of posts out here on my quest for a solution without writing the totals to my tables and finally came up with a very simple one using bits a pieces from various posts. My goal was to have a liberal order form in which I gave the user a quantity default based upon the quantity on hand (original quantity - previously ordered), but the flexibility to override the default. I had frm_Invoices (main) and subfrmInvoiceDetails (based upon a query) my quantity field was bound to tbl_InvoiceDetails in the query because I wanted this to get updated for the quantity entered as each item is ordered. Also my Quantity validation rule was >0. I got my original quantity by using a combo box on the inventory number in which quantity is a column. In my subform in the Quan control, got focus event this is the code I used.Private Sub Quan_GotFocus()
On Error GoTo Err_Quan_GotFocus
Me!Quan = Me.InvNo.Column(3) - DSum("Quantity", "tblInvoiceDetails", "[InventoryNumber] = '" & [InvNo] & "'")
Err_Quan_GotFocus:
MsgBox "This item is OUT OF STOCK!", 0
End Sub
NOTE: [InventoryNumber] is the name of the field defined in the table.
[InvNo] is the name of the control in the subform.
