Inventroy totals

[June7]

Sorry, thought I had fixed my post to correct the expression. However, the current data will show a negative available for ProductID_PK 8 because tbleAssigned shows 12 assigned but Quantity in tblProducts shows only 8. I deleted some records from tbleAssigned and now get positive calc with correct expression.

Now I see the + button is not intended to alter the Quantity value.

[sprtrmp]

i am seeing negative numbers, even when the quantity is a positive number. if there are 10 of an item, and i issue 1, the remaining quantity shows up as -9

is there a way to update the "Available" control when cmbHRDescription is selected, that would give me a quantity in the available control so that i can compare it to the quantity assigned to see if there are enough available?

something like:

if Available >= quantAssigned then
some command
else
msgbox "Not enough in inventory"
endif

as it is right now, there is nothing in "Available" to compare to make sure the user isn't trying to assign more than is available.
(I should have become a dentist)

[June7]

Did you change Size/Description combobox ColumnCount and ColumnWidths properties to account for adding the Quantity field in the RowSource?

The Available control should only update when a new record is committed. Your code will have to compare the Available against the quantity input and determine whether or not to allow the record commitment.

[sprtrmp]

All of the changes were made as described, but, as "Orange" pointed out, the larger number is being subtracted from the smaller. Looks to me that the way the DSum statement is written, the overall quantity is being subtracted from the quantity assigned.

[June7]

Right, I got the expression backwards? Did you change? Did you see post 16? Post the expression you are using.

[sprtrmp]

Sorry, I am only able to work on this thing with one half of my brain. It is working OK now I changed the expression to

=[cmbHRDescription].[column](2)-(DSum("QuantAssign","tbleAssigned","ProductID_FK=" & [cmbHRDescription]))

[sprtrmp]

Just an FYI, I got it pretty much working the way I need it to. First, I added "available.requery" to the after update event of cmbHRDescription, this updates the quantity shown as available.

I also added an Nz() function to the DSum expression:

=[cmbHRDescription].[column](2)-Nz(DSum("QuantAssign","tbleAssigned","ProductID_FK =" & [cmbHRDescription]))

to handle the Null values that the function was returning where there were no values assigned to individual items.

everything seems to be working well enough for now.

Thanks a bunch, couldn't have done it without the help!!!