Validation Rule in Before Update event, restrict entry to positive integer >0

[Phred]

Intermediate skill level VBA, access 2007, no compile errors, single user, In development, not deployed. Windows 2007 professional. Data normalized. Problem with validation rule on form.



I have a Bound Inventory field on a form (bound to Tbl_MasterInventory). The end user enters a new inventory number on the form.

I am writing this in the "Before Update" event of the Bound Control "PUonHand" (Packing Units On Hand) so I can undo an entry gracefully if the number is incorrect.

I can prevent the negative numbers with the first test.

I need to restrict the entry to Positive Integers. In other words no fractions like 4.30. I don't want to use the field properties of the table. I want it to call a message box and undo the entry restoring the field to the previous value.

If the end user enters a fraction the test should catch it, fire a message box stating the number must be 0 or a whole positive number.

'__________________________________________ This test for <0 works fine.
Private Sub PUOnHand_BeforeUpdate(Cancel As Integer)
If (Me.PUOnHand) < 0 Then
Call MsgBox("The PU On Hand field cannot be negative." _
& vbCrLf & "" _
& vbCrLf & "You must enter 0, or any positive, whole number." _
& vbCrLf & "" _
& vbCrLf & "This change will be undone." _
, vbCritical, "Negative Number")
Me.Undo

Exit Sub
End If

'_________________________________________

If the number is a positive integer it is good. no other tests are necessary.

I can find info that talks about it but does not really address it clearly enough for me.

I can't figure out the test. I've tried a bunch of stuff. Here is the hacked up different stuff I have tried.

If (me.PUOnHand)is not like"*[!0-9]*" then
(not like "*[!0-9]*"(Me.PUOnHand))=true then
Is Null OR Not Like "*[!0-9]*"
Int(me.PuonHand)=false
cint(Me.puonhand)

Call MsgBox("The PU On Hand field cannot be a fraction." _
& vbCrLf & "" _
& vbCrLf & "You must enter 0, or any positive, whole number." _
& vbCrLf & "" _
& vbCrLf & "This change will be undone." _
, vbCritical, "Fraction")
Me.Undo

Exit Sub

End If

Help is always appreciated.

Phred

[ssanfu]

1) In the table, set the field type to Integer, or Long Integer. Easiest method

2) In code:

If me.PUOnHand <> Int(me.PUOnHand) Then
Call MsgBox("The PU On Hand field cannot be a fraction." _
            & vbCrLf & "" _
            & vbCrLf & "You must enter 0, or any positive, whole number." _
            & vbCrLf & "" _
            & vbCrLf & "This change will be undone." _
            , vbCritical, "Fraction")
        Me.Undo
End If

[Phred]

Geeez, I knew it was gonna be simple but this is embarrassing.

Cheers and thank you,

Phred