Edit Checks - No Alpha Characters Allowed

[StacyStacy]

I think this may be an easy question: In addition to validating a text field to ensure that a 3-digit number is entered, I also need to reject any alpha characters. Can I perform data validation on a numberic field?

Please help. Here's my code:

Private Sub Provider_Code_BeforeUpdate(Cancel As Integer)

If IsNull(Me.Provider_Code) Or Len(Me.Provider_Code) < 3 Then
MsgBox "Please Enter A 3-Digit Privider Code!"
Cancel = True
End If

Dim MyVar As Variant
MyVar = DLookup("[PROVIDER_CODE]", "[TT_PRIMARY PROVIDER_CODE]", "[PROVIDER_CODE] ='" & Me.Provider_Code & "'")
If MyVar = Me.Provider_Code Then
MsgBox "This Provider Code Already Exists. Please Assign A Different Code!"
Cancel = True
End If

End Sub

 

[pdx_man]

Have you checked out the Input Mask in Access Help? This may be an easier way for you to go.

Use:
000
For digit only, entry required.

 

[StacyStacy]

But that will allow for any 3 character input, whether alpha or numberic ... correct?

 

[pdx_man]

That Input Mask would be:
AAA

Give it a try, but look it up in Access Help. It will give you all the codes.

 

[StacyStacy]

I've already setup many edit masks and it works. How would I turn off the system error message and use my own?

 

[ghudson]

I do not think that you can turn off the built in error message when an entry does not follow the parameters of the input mask.

You could set up your own validation and check the results in the fields before update or after update event.

Search the forum for I know that the are code samples on how to use the KeyDown or KeyPress events to trap for what you do not want [non numeric] keyed into your text box.

Also, check the help files for the IsNumeric function for that might work for you.

HTH