Validation Rule for Reminding Users: "Whole Numbers Only!"

[tbl]

For Integer fields, Access will silently round a fractional
entry.

I'd like to prevent rounding and cause a message box to
appear.

But I can't figure out quite how to do it.

I've tried...

If Me.txtNumAngler <> Int(Me.txtNumAngler) Then

.... for starters, but that doesn't seem to work, and I don't
know why.

 

[Allen Browne]

Use the AfterUpdate event procedure of the text box where the number is
entered. Search for the decimal point in the Text property.

Like this:

Private Sub Text0_AfterUpdate()
With Me.Text0
If Val(.Text) <> .Value Then
MsgBox "Will be rounded to " & .Value
End If
End With
End Sub

 

[J_Goddard via AccessMonster.com]

I think this would be better put in the Before Update event - because that
event can be cancelled before invalid input is accepted.

And is it possible that the value being tested in the After Update event has
already been changed to integer?

John

Use the AfterUpdate event procedure of the text box where the number is
entered. Search for the decimal point in the Text property.

Like this:

Private Sub Text0_AfterUpdate()
With Me.Text0
If Val(.Text) <> .Value Then
MsgBox "Will be rounded to " & .Value
End If
End With
End Sub

For Integer fields, Access will silently round a fractional
entry.

[quoted text clipped - 10 lines]

... for starters, but that doesn't seem to work, and I don't
know why.

 

[Fred Boer]

Dear tbl:

I hesitate to jump in, but I think Allen might have missed that you wanted
to prevent the insertion of the rounded value. You may wish to use the
Before Update rather than the After Update event so that you can prevent the
rounded value being entered.

Perhaps something like:

Private Sub test_BeforeUpdate(Cancel As Integer)

If Val(.Text) <> .Value Then
If MsgBox("The entered value will be rounded to " & .Value & ". Do you
wish to accept the rounding?", vbYesNo) = vbYes Then
Else
Cancel = True
End If
End If

End Sub

HTH
Fred Boer

 

[Allen Browne]

The poster did say "prevent", so the BeforeUpdate event would be the way to
do that.

Same code, just use:
Cancel = True

The Text property will show what's in actually in the control even though
the Value has been rounded, so comparing the Text against the Value will
indicated if there is a difference.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I think this would be better put in the Before Update event - because that
event can be cancelled before invalid input is accepted.

And is it possible that the value being tested in the After Update event
has
already been changed to integer?

John

Use the AfterUpdate event procedure of the text box where the number is
entered. Search for the decimal point in the Text property.

Like this:

Private Sub Text0_AfterUpdate()
With Me.Text0
If Val(.Text) <> .Value Then
MsgBox "Will be rounded to " & .Value
End If
End With
End Sub

For Integer fields, Access will silently round a fractional
entry.

[quoted text clipped - 10 lines]

... for starters, but that doesn't seem to work, and I don't
know why.

 

[tbl]

Dear tbl:

I hesitate to jump in, but I think Allen might have missed that you wanted
to prevent the insertion of the rounded value. You may wish to use the
Before Update rather than the After Update event so that you can prevent the
rounded value being entered.

Perhaps something like:

Private Sub test_BeforeUpdate(Cancel As Integer)

If Val(.Text) <> .Value Then
If MsgBox("The entered value will be rounded to " & .Value & ". Do you
wish to accept the rounding?", vbYesNo) = vbYes Then
Else
Cancel = True
End If
End If

End Sub

HTH
Fred Boer

Thanks to all who replied. Here's what I have settled on,
so far:

*****
Private Sub txtAnglers_BeforeUpdate(Cancel As Integer)

If Val([txtAnglers].Text) <> [txtAnglers].Value Then
Cancel = True
MsgBox ("Fractions are not allowed! You may need _
to interview the clerk to determine the correct
whole number.")
End If

End Sub
*****

It *seems* to work ok...