Restricting Entry in a text box to numeric values

[jeffreyccs]

Hi

I have a query regarding the above, I have tried using the keypress event to restrict entry to numeric values only and that functions correctly. The issue that I have is that there are a number of textboxes and like most people I will use the enter or tab keys to move between them. unfortunately the on keypress event does not work with enter or tab and triggers the error message.

Private Sub tb1l_KeyPress(KeyAscii As Integer)

If (KeyAscii > 47 And KeyAscii < 58) Or (KeyAscii = 8) Or (KeyAscii = 9) Or (KeyAscii = 10) Then
      KeyAscii = KeyAscii
      Else:
      MsgBox "Numberic Data Only!", vbOKOnly, "Invalid Data"
      KeyAscii = 0
   End If
  
End Sub

I would be grateful if anyone has an alternative method that I could try.

 

[Gasman]

Why aren't the fields for those controls numeric?

See if you can use the Regex expressions here.

how create query to check data

Hi All. How to create 4 type of qury: 1. to retreive data where string field has only numeric data. 2. to retreive data where string field has onlt alphabitic data. 3. to retreive data where string field has only alphanumeric data. 4. to retreive data where string field has data with special...

www.access-programmers.co.uk

 

[Mike Krailo]

unfortunately the on keypress event does not work with enter or tab and triggers the error message.

You should use the Forms BeforeUpdate event for that sort of thing. Just get rid of your key press code and use this instead.

If IsNull(Me.txtMyControl) Or Not Me.txtMyControl.TEXT Like "*[!0-9]*" Then
    MsgBox "number must be an integer"
    Cancel = True
End If

 

[jeffreyccs]

Many thanks for your input

Regards

Jeff

 

[theDBguy]

unfortunately the on keypress event does not work with enter or tab and triggers the error message.

You could try the KeyDown event...

 

[ebs17]

Or you give the Format property of the unbound text box a number format. Then only numbers are accepted.
A bound text box accepts definitions of the associated table field, which means that only numbers can be entered in a number field.

 

[moke123]

Private Sub Text0_KeyPress(KeyAscii As Integer)

    AllowOnlyNumbers KeyAscii, Me.Text0

End Sub


Public Sub AllowOnlyNumbers(ByRef KeyAscii As Integer, ctl As TextBox)


    Select Case KeyAscii

        Case 48 To 57           '0 to 9
            'do nothing
        Case 46                 'comment out if a "." is not needed  (ie. 2.1)
            'do nothing


        Case Else
       
            MsgBox "Only Numbers Permitted"
            ctl.SetFocus
            ctl.SelStart = Len(ctl.Text)
            KeyAscii = 0
     
    End Select
     
End Sub

 

[Pat Hartman]

Access provides a no code solution which is what ebs17 suggested. Call me lazy, call me crazy but I make it a practice to not write code to do something that Access does natively.

If I were going to write code to validate a field (which I do for most data fields), I would put the code where it would do the most good which is the form's BeforeUpdate event as suggested by Mike. That way I can prevent the record from being saved if there is invalid data.

PS Mike, the .text property is only available when the control has the focus so I'm assuming you are using the on Change event for this code.

 

[Mike Krailo]

To be honest, I was doing a rendition of it in the before update and quickly realized that it was not working as well as the simple formatting of the control as a number. So I tend to agree with ebs17 in this case.

 

[Pat Hartman]

If all you care about is that the field is numeric, then the property setting solves the problem and no code is required. usually though, I tend to have sanity checks on fields where it makes sense so just being numeric or a valid date isn't good enough. I'm sure you've run into applications (always written by someone else) where you see 12/3/201 or something similar. As far as Access is concerned, this is a valid date. For most applications though it is not rational. Hence - I recommend sanity checks for dates and numbers at least. DOB and DOD can't be in the future for example. You might also decide on a date range, maybe 18-65 years prior to today for DOB if you are looking at employee data. Each application has different "rules" for what might be rational. Most businesses won't hire people < 18 but they will occasionally hire people older than 65. So the first should trigger a hard NO but the second should trigger a question?