Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Question Unanswered: Limit alpha characters in a text field

    Hello All,

    I am trying to limit alpha entries in my text box, that is only used for numeric values, how would i go about doing this? Thanks

    Chris

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Two ways you can do it, you can either use an Input Mask in the text boxes property, like so <??????? where the question mark represents a character. Or, you can use code in the After Update event of the text box to convert to lower case. Like so,

    'Routine is run after Text2 text box is updated.

    On Error GoTo Err_Text2_AfterUpdate

    Text2 = Trim(Text2) 'remove preceeding spaces
    Text2 = StrConv(Text2, vbLowerCase) 'convert to lower case


    Exit_Text2_AfterUpdate:
    Exit Sub

    Err_Text2_AfterUpdate:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Text2_AfterUpdate

    The second option does not rely on the number of characters as in the first option, but the choice is yours.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree with djn.

    however Id suggest in preference putting your validation code in the BEFORE update event (either form or control), or if you really want to annoy your users consider another mechanism. One technique is to place some code in the forms lost focus (or even keypress/keyup) event that forces a validation.

    I have seen the technique successfully used by changing the background colour of items that fail validation (if you go down that route also force the colours to be set on the after insert event). In that approach the background colours were white, unless it was a required field (which was yellow), or if the field was invalid (which was red). the developer used the on change & lost focus events to set the appropriate background colours
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I think DJN misread the original post:
    my text box, that is only used for numeric values
    The input mask for numerical characters only would be 9999999, being sure to use the maximum number of digits that would be entered i.e. a mask of 99999 will accept 210 but will truncate 210000 to 21000.

    If you format the field a General Number and a non-numeric entry is made, a native Access warning will popup explaining that the data entered is not appropriate and give the example that non-numeric data has been entered in a numeric field.

    Validation can be put in the Before_Update event, as suggested previously, with:
    Code:
    Private Sub MyNumericalField_BeforeUpdate(Cancel As Integer)
         DoCmd.SetWarnings False
         If Not IsNumeric(MyNumericalField) Then
              MsgBox "This field is numerical only!"
         End If
         DoCmd.SetWarnings True
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Yeah, I misread it, getting late and I am going home!

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You Brits are so soft! 4:19 and you're already beginning to flag!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Oct 2006
    Posts
    110
    Thanks for the replies, Missinglinq, Can I just add:

    Private Sub MyNumericalField_BeforeUpdate(Cancel As Integer)
    If Not IsNumeric(MyNumericalField) Then
    MsgBox "This field is numerical only!"
    End If

    This does work, but how do I keep it from going to the next field after clicking ok on the message?

    .setfocus isnt working on the beforeupdate action.

    Thanks for your help.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Add

    Cancel = True
    Paul

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I cannot get it to reset the focus, even with Cancel = True. Finally changed it to the Exit event, which does work.

    Code:
    Private Sub MyNumericalField_Exit(Cancel As Integer)
    If Not IsNumeric(MyNumericalField) Then
        MsgBox "This field is numerical only!"
        MyNumericalField.SetFocus
    End If
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Cancel = True

    in the before update event will stop the update and leave focus in that field. If it didn't work that way for you, something was entered wrong. This is a common way of verifying data entry.
    Paul

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Yoour advice always dead on in the past, so I chewed at it and chewed at it, finally deleted the text box and placed a new one on form and son-of-a-gun, it finally worked!

    Thanks, Paul!

    For cmays637, finally ended up with this:

    Code:
    Private Sub MyNumericalField_BeforeUpdate(Cancel As Integer)
       If Not IsNumeric(MyNumericalField) Then
            Cancel = True
            MsgBox "This field is numerical only!"
       End If
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    Oct 2006
    Posts
    110
    Thanks to everyone for your help.. This is great! The code works perfectly.

  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Another approach that I thought for sure someone would have brought up is using the On Key Press event. You need to first turn on Key Preview for the form. Then in the On Key Press put something like:

    Code:
        If Not (KeyAscii >= Asc("0") And KeyAscii <= Asc("9")) Then
        
            Beep
            KeyAscii = 0
            
        End If
    You can take out the Asc calls, but I think it makes the code easier to read.

  14. #14
    Join Date
    Oct 2006
    Posts
    110

    Question

    Hi Guys.. .Noticed a little problem just now, when I have the remove the information and try to exit the field, I get a "Invalid use of null" error message.

    Private Sub TRACKING2_BeforeUpdate(Cancel As Integer)
    If (Me.TRACKING2.Value <> "n/a") And Not IsNumeric(Replace(Me.TRACKING2, "-", "")) Then
    MsgBox "This Field Must Be N/A or a Numeric Entry."
    Cancel = True
    End If
    End Sub

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider usign the IsNUll function to detect Nulls, or possibly nz
    Quote Originally Posted by cmays637
    Hi Guys.. .Noticed a little problem just now, when I have the remove the information and try to exit the field, I get a "Invalid use of null" error message.

    Private Sub TRACKING2_BeforeUpdate(Cancel As Integer)

    If isNull(Me.TRACKING2.Value) = TRUE OR (Me.TRACKING2.Value <> "n/a") And Not IsNumeric(Replace(Me.TRACKING2, "-", "")) Then
    MsgBox "This Field Must Be N/A or a Numeric Entry."
    Cancel = True
    End If
    End Sub
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •