Hi Guy's, can someone tell me where i am going wrong here please, i am adding ie: 15 new stock items and the 6 digit number am wanting to lookup or count if i have added these into another table, if i have, the status would be planning or oh hold
so we may have added 3 of these but still waiting stock so the answer i am looking for is StartQty = 15, Allocated = 3 NewQty = 12
I MEANT TO ADD THAT THE RESULT IS ADDING as allocated 10709 and NewQty -10694 ! Bizarre
Also if we have added 3 of these in tblEdit, they are always added with this 6 digit number first then maybe followed with hypen and client name
Code:
Dim mHold As String, mPlan As String
Dim mLiftNo As Long, mStartQty As Long, mAllocatedQty As Long, mNewQty As Long, mAllocated As Long
mStartQty = Me.StartQty
mLiftNo = Me.LiftNo
mHold = "On Hold"
mPlan = "Planning"
If mStartQty > 0 Then
If IsNull(mAllocated = DCount("LiftNo", "tblEdit", "[LiftNo] Like ""*" & mLiftNo & "*"" And [Status] = '" & mHold & "'" & " Or '" & mPlan & "'")) Then
Me.NewQty = Me.StartQty
Me.AllocatedQty = "0"
Else
mAllocated = DCount("LiftNo", "tblEdit", "[LiftNo] Like ""*" & mLiftNo & "*"" And [Status] = '" & mHold & "'" & " Or '" & mPlan & "'")
Me.AllocatedQty = mAllocated
Me.NewQty = Me.StartQty - mAllocated
End If
End If