We help IT Professionals succeed at work.

Microsoft Access - limit entry to numbers and letters

jrmcanada2
jrmcanada2 asked
on
204 Views
Last Modified: 2017-05-13
I have a text box and I want to limit the user to entering only letters and numbers in it. In particular, no spaces should be allowed. The entry can have up to five characters. I'd use an input mask of AAAAA except that the user needs to be able to enter values with fewer than five characters in some cases.

It doesn't matter to me if the solution rejects the characters as the user types them or if it waits until they've completed the entry for the field and then validates the entry.

I could just write a routine to cycle through each character and verify that it's a letter or number but I'm curious if there's an easier way.

It's an unbound field so I can't verify it at the table level.

Thanks.
Comment
Watch Question

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
You do have two solution paths.  You can use the _KeyPress() event and inspect the value of the KeyAscii parameter.  If it isn't in the range you want [0-9A-Z] then you can set it to zero to prevent it from entering the textbox.  I usually use the Beep statement to give the user some audible feedback.

The other is the _Exit() event to validate all of the characters.  You can either iterate the characters in the string or use some pattern-matching magic.  You could use the LIKE operator of the Regexp object to do this.

Like example:
If txtbox LIKE "*[!0-9a-z]*" Then
    Cancel = True
    Beep
    txtbox.color = vbYellow
End If

Open in new window

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
You also have to count the characters

If Len(Me.txtbox) > 0 and Len(Me.txtbox) <= 5 then
Else
    Cancel = True
    Exit Sub
End If

Open in new window

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
@Pat

I assumed the maxlength property was set on the textbox and the length wouldn't need to be checked.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
That should work also but I would not use the form property.  I would use the table property.  Setting the length on the table is much easier to find and modify should that be necessary.  I'm not sure where I got the idea that the field size was not defined in the table.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can skip the inputmask (which often bothers users) and run this code in the AfterUpdate event:

If Not IsNull(Me!YourTextBox.Value) Then
    Me!YourTextBox.Value = Left(Join(Split(Me!YourTextBox.Value, " "), ""), 5)
End If

Open in new window

It will clean up and limit the input silently

/gustav
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
@jrmcanada2

Are you only worried about space characters or any non-alphanumeric characters?

Author

Commented:
@aikimark

I'm trying to prevent spaces and any other non-alphanumeric characters. I just want a-z,A-Z,0-9
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:

Author

Commented:
Thank you!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.