BeforeUpdate: Why is 50 greater than 250?
In response to post number 14 in this thread https://www.accessforums.net/showthr...905#post335905
(where OP wants to know the number of pallets needed to contain n received items AND know the remainder on a partial pallet if there is any) I am submitting the details of a problem I encountered while proposing a solution for that thread. The intent is to try to save everybody else some hair pulling, but then again, it would not surprise me to know I am the only so-called 'expert' on the planet that did not know the cause. BTW, I think this forum tagged me as a VIP, but that's a different issue.
I created a form with 4 unbound textboxes - txtRecdQty, txtPallets, txtStack, txtMod (Stack is the term OP used to define the number of items that would be stacked on a pallet). One of the data validations I wanted to impose in the BeforeUpdate event of txtStack was to not allow txtStack to be greater than txtRecdQty since a stack amount could not be greater than the amount received. If I put 250 in txtRecdQty and 50 in txtStack, the validation message triggered and said that the stack quantity could not be greater than the received quantity. Of course, this does not make sense. Here is that portion of the code, followed by everything I could think of to test in the immediate window with the answers on the same line to save space:
Code:
If Me.txtStack > Me.txtRecdQty Then
MsgBox "Stack quantity cannot be greater than received quantity"
Cancel = True
bolResetStack = True
With Me.txtStack
.Undo
.SelStart = 0
.SelLength = Len(Me.txtStack)
End With
'Me.Refresh
Exit Sub
Else
setCtlSources False
End If
?me.txtRecdQty.Oldvalue 100
?me.txtStack.Oldvalue 20
?me.txtRecdQty.value 100
?me.txtStack.Value 20
?Me.txtStack.oldvalue > Me.txtRecdQty.OldValue True
?Me.txtStack > Me.txtRecdQty True
?me.txtStack.Value > me.txtRecdQty.Value True
?isnumeric(Me.txtStack) True
Additional info:
I was probably using different values when performing the following, but the immediate window tests shown were based on 100 and 20.
- Validation was sometimes correct when form was opened the first time.
- If not, compacting/closing/reopening db might fix it for one or more iterations.
- If not, a complete restart of the laptop might fix the problem for one or more iterations.
Once I stuck with 240 received and 50 for the stack, it always failed to evaluate properly, regardless of repeating those attempts to fix it.
Hopefully I provided enough clues. Anyone want to guess why txtStack (50) is greater than txtRecdQty (250)? I hate to say how long it took me to figure it out.
If you think of a possible cause not already posted, take a stab at it. I'll wait for a few guesses before spilling the beans.
Last edited by Micron; 10-25-2016 at 08:20 PM.
Reason: added on
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.