OK, there were a few things wrong.
You forgot to include the procedure’s argument, rlngAdjustmentID, both in the procedure’s declaration and in the call to the procedure.
The procedure declaration should be:
Code:
Public Sub AllocateConsumption(rlngAdjustmentID As Long)
And the call to the procedure:
Code:
Mod_AllocateConsumption.AllocateConsumption Me.AdjustmentID
There are some spelling mistakes – all mine I think, sorry! – but it was difficult without having the actual table definition.
My procedure tested something called AdjustmentType which does not exist; the correct name is AdjustType.
Code:
ElseIf rstSparesAdjusted!AdjustType <> "Consumption" Then
Similarly SparesID should be SpareID
Code:
lngSpareID = rstSparesAdjusted!SpareID
Code:
"WHERE SpareID = " & lngSpareID & " AND (QtyAdjusted - Nz(ConsumedQTYfromReceipts,0)) > 0 " & _
Again QtyAdjust should be QtyAdjusted
Code:
lngQtyAvailable = !QtyAdjusted - Nz(!ConsumedQtyfromReceipts, 0)
The definition of ConsumedQtyfromReceipts has no default value of zero so nulls are possible. There are 5 lines to be changed to include a Nz(,0)
Code:
"WHERE SpareID = " & lngSpareID & " AND (QtyAdjusted - Nz(ConsumedQTYfromReceipts,0)) > 0 " & _
Code:
lngQtyAvailable = lngQtyAvailable + !QtyAdjusted - Nz(!ConsumedQtyfromReceipts, 0)
Code:
lngQtyAvailable = !QtyAdjust - Nz(!ConsumedQtyfromReceipts, 0)
Code:
!ConsumedQtyfromReceipts = Nz(!ConsumedQtyfromReceipts, 0) + lngQtyToAllocate
Code:
!ConsumedQtyfromReceipts = Nz(!ConsumedQtyfromReceipts, 0) + lngQtyAvailable
I have made these changes and return the database.
new.zip
Two points:
- I recommend you always include Option Explicit at the start of every module.
- There is no check to determine whether a receipt has already been allocated.