Mathematical fields

[klp via AccessMonster.com]

I have a field on my customer orders form that when they enter in an order
qty it has to be an even multiple of a dictated number. For example if have
4500 parts on a roll, I have to sell even rolls - it cannot be a qty that is
not divisible by 4500. So if it has a remainder then I want an error message
to pop up telling me the order qty must be a multiple of roll qty and provide
me w/ what the order multiple is. And keep the loop going until it reaches
it's correct multiple. How does access handle remainders and how should I go
about doing this? Should I use a query or can I do it beneath the form?

Thanks in advance

 

[fredg]

I have a field on my customer orders form that when they enter in an order
qty it has to be an even multiple of a dictated number. For example if have
4500 parts on a roll, I have to sell even rolls - it cannot be a qty that is
not divisible by 4500. So if it has a remainder then I want an error message
to pop up telling me the order qty must be a multiple of roll qty and provide
me w/ what the order multiple is. And keep the loop going until it reaches
it's correct multiple. How does access handle remainders and how should I go
about doing this? Should I use a query or can I do it beneath the form?

Thanks in advance

If [OrderQty] Mod 4500 = 0 Then
' No Remainder. Order is OK
Else
' There is a remainder. Order is not OK
End If

i.e.
9000 Mod 4500 = 0 is True
13500 Mod 4500 = 0 is True
9001 Mod 4500 = 0 is False
9100 Mod 4500 = 0 is False
etc.

 

[Guest]

Use the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate (Cancel As Integer)

Dim intSuggestion As Integer

If Nz(OrderQty, 0) <= 0 Then
MsgBox "Please enter order quantity"
OrderQty.SetFocus
Cancel = True
Else
If OrderQty Mod RollQty > 0 Then
intSuggestion = ((OrderQty \ RollQty) + 1) * RollQty
MsgBox "Invalid order quantity - can not order part rolls. " &
vbNewline & _
"Suggested order quantity is " & intSuggestion & ".
Please confirm."
OrderQty = intSuggestion
OrderQty.SetFocus
Cancel = True
End If
End If

End Sub

 

[klp via AccessMonster.com]

Great thank you!! Worked great. But I have another question that I forgot to
mention in my post. We can override the qty if it's not even(certain
circumstances) so can we put on the message box and override button? Or if
not how would I go about doing that?

Use the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate (Cancel As Integer)

Dim intSuggestion As Integer

If Nz(OrderQty, 0) <= 0 Then
MsgBox "Please enter order quantity"
OrderQty.SetFocus
Cancel = True
Else
If OrderQty Mod RollQty > 0 Then
intSuggestion = ((OrderQty \ RollQty) + 1) * RollQty
MsgBox "Invalid order quantity - can not order part rolls. " &
vbNewline & _
"Suggested order quantity is " & intSuggestion & ".
Please confirm."
OrderQty = intSuggestion
OrderQty.SetFocus
Cancel = True
End If
End If

End Sub

I have a field on my customer orders form that when they enter in an order
qty it has to be an even multiple of a dictated number. For example if have

[quoted text clipped - 6 lines]

Thanks in advance

 

[Guest]

Minor adjustment required. Keep the rest of the code the same just change
this If block:

If OrderQty Mod RollQty > 0 Then
intSuggestion = ((OrderQty \ RollQty) + 1) * RollQty
Select Case MsgBox("Requested order quantity is not an even
number of rolls. " & vbNewline & _
"Would you like to round this up to " &
intSuggestion & "?" & vbNewline & vbNewline & _
"Choose 'No' to keep your original order quantity or
'Cancel' to enter a new quantity.", _
vbQuestion + vbYesNoCancel, _
"Confirm order quantity...")
Case vbYes
OrderQty = intSuggestion
Case vbNo
'No action required
Case vbCancel
OrderQty.SetFocus
Cancel = True
End Select
End If

 

[klp via AccessMonster.com]

Okay works great. Now I have another question. Is it possible not to round?
Say I have an amount w/ decimals (13.5) for example I do not want it to round
to 14. Is this possible?