prevent alpha in numeric field

[NigelShaw]

hi All,

i am using this code-

Private Sub MatIn_BeforeUpdate(Cancel As Integer)
If Not IsNumeric([MatIn]) Then
MsgBox ("The Entry must contain a number"), vbOKOnly, "AccCIS Input Error"
Cancel = True
Exit Sub
End If

to prevent letters being added to a currency field box. there was a Trim added here-
If Not IsNumeric(Trim, [MatIn]) Then
but it threw out an error.

anyone know what it should be?

many thanks

nigel

 

[raskew]

Hi -

Try: If Not IsNumeric(Trim([MatIn])) Then

HTH - Bob

 

[NigelShaw]

Hi bob,

thanks for that. it is behaving a bit perculiar though.

with the code in the BeforeUpdate

if i place a letter in the field, i get a standard Access error, i remove the text and click out of the field. Then i get the message!

bizzare

nigel

 

[raskew]

Nigel -

You might want to play with this:

Public Function fSaveNumer2(pPhone As String) As String
'purpose:  Removes all non-numeric characters from a
'          string.
'coded by: raskew
'calls:    IsNumeric()
'Input:    ? fSaveNumer2("(800)-555-1212")
'Returns:  8005551212
'

Dim strHold As String
Dim strKeep As String
Dim intLen  As Integer
Dim n       As Integer

   strHold = pPhone
   intLen = Len(strHold)
   For n = 1 To intLen

   Next
    On Error GoTo E_Handle

fExit:
    On Error Resume Next

    Exit Function
E_Handle:
    MsgBox Err.Description & vbCrLf & "fSaveNumer2", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

Bob

 

[NigelShaw]

hi bob,

Many thanks for your help. I will be trying it later

Nigel

 

[georgedwilkinson]

I have to ask the obvious: why don't you just make the field numeric?

 

[NigelShaw]

hi georged,

To be honest, I don't like the standard access not in field message. Also, I wanted to try and control it more for the user. I have a code in keypress checking ASCII values but didn't want a case statement from a-z etc so tried using my post. Can the standard be changed for custom messages then ?

Thanks

Nigel

 

[Banana]

Yes; look at Validation Text property in Access help.

 

[NigelShaw]

hi

It's funny. You sometimes completely overlook the standard options available in access and go for a more difficult solution that you assume is easier!

Many thanks guys

Nigel