Making unbound textbox numeric only

[newbieX]

I have placed the code below in the validation section of an unbound textbox (tbx_NumOrdered) of the property sheet on my data entry form

Is Null Or Not Like "*[!0-9]*"

And this in the Validation Text

Please enter a whole number!

Two issues:

1. The Validation rule does not fire when the value is Null. It does work when any other non-numeric value is entered. No other coding for this box exists.
2. I would like to reset the value to "0" after the message but am not sure how (or where) to code it.

Insights into solutions anyone?

[andy49]

Nice piece of code here may help...

https://www.access-programmers.co.uk...d.php?t=132788


Sent from my iPhone using Tapatalk

[newbieX]

I saw this code but really wanted to avoid referring to KeyAscii...

Nice piece of code here may help...

https://www.access-programmers.co.uk...d.php?t=132788


Sent from my iPhone using Tapatalk

[aytee111]

Why don't you use the Format property of the textbox and set it to a number?

[June7]

Depending on Format property alone doesn't allow custom message.

[Missinglinq]

Straight from the horse's mouth:

When data is entered that violates the Validation Rule setting, you can use the Validation Text property to specify the message to be displayed to the user.

The important part, here, is that part in red. The Validation Rule is designed to check that data which is actually entered adheres to the rule...it cannot check something that has not been entered,i.e. Null.

The usual place to check whether or not a Control is Null is in the Form_BeforeUpdate event...simply because until the user goes to save a Record, the Access Gnomes have no way to tell if the user has actually left it blank. Testing in an event tied to the Control (OnExit, for example) is useless, because all the user need to do to defeat the validation is to simply not enter the Control!

To me, this whole thing begs the question of what the OP is actually using the data in this Unbound Control for. Is it being used in a Calculation...if not, how is it being used? If used in a calculation, how is it being triggered? By a Command Button? Prior to the Record being saved? If the former, the validation can be done in the OnClick event of the button. If the latter, in the Form_BeforeUpdate event, as mentioned before.

Linq ;0)>

[Micron]

AFAIK, IsNumeric(Me.tbx_NumOrdered) will return False if the value is Null or contains non numeric characters, so the test should be simpler than what I see in the first post.
Sample air code:

Private Sub tbx_NumOrdered_BeforeUpdate(Cancel As Integer)
If Not IsNumeric(Me.tbx_NumOrdered) Then 
  Msgbox ("Please enter a whole number!")
  Cancel = True
End If

Depending on the need, this check might be done at the control or form level. If I want to validate several controls this way, I create a loop and find those controls whose tag value was set to 'Reqd' then pass them to a function that validates them accordingly. Having a module level function makes the task available to any form in the db.

[Missinglinq]

The tbx_NumOrdered_BeforeUpdate event will not be fired if the user doesn't enter the Control, or enters the Control but doesn't enter anything, making it a no go for this testing for Null.

Also,Decimal Points, Commas, Plus Signs and Negative Signs are all considered to be 'numeric' by IsNumeric(), which would probably make it useless, in this application, although, as I said, we really need to know what this value is being used for.

Linq ;0)>

[Micron]

Right you are again - I was focused on the entry not being null but being a number as opposed to a numeric character only, though I did say the form update event was a possibility. Also, using the technique I use, it matters not if the user enters the control because it is validated as a group based on the tag. The event can be just about anything, including a button click.