none
[Access 2k3 + VBA] Preventing users from inserting char values in textbox not working in module RRS feed

  • Question

  • Hi,

    I've made a procedure to limit users to input only numbers and no characters, the works if I put it in the form VBA, but if I move it to a module and then call it, it does not work...

    Here is the code in the form:

    'Prevent user from input a char value 
    Public Sub TextBox1_KeyPress(KeyAscii As Integer)
        MOD_VALUES_LIMITS.LimitTextBox1
    End Sub
    

    and in the module:

    'Prevent user from input a char value 
    Public Sub LimitTextBox1()
        Select Case KeyAscii
            Case Asc("0") To Asc("9")
            Case Asc("-")
                If InStr(1, [Form_Name].[TextBox1].Text, "-") > 0 Or [Form_Name].[TextBox1].SelStart > 0 Then
                    KeyAscii = 0
                End If
            Case Asc(".")
            If InStr(1, [Form_Name].[TextBox1].Text, ".") > 0 Then
                KeyAscii = 0
                End If
            Case Else
                KeyAscii = 0
        End Select
    End Sub
    

     

    How can i fix this, please?!

     

    Regards

    Friday, December 23, 2011 5:37 PM

Answers

  • Ok, sorry for the late response, did not had the time to fully test the function.

    Here the latest small correction to get the code working in your Forms TextBox _keypress event.

     

    Modify the Function to below code:

    Public Function ChckOnlyNumbers(KeyAscii As Integer)
    
    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl
    
    Select Case KeyAscii
        Case Asc("0") To Asc("9")
        Case Asc("-")
            If InStr(1, ctrl.value, "-") > 0 Or ctrl.SelStart > 0 Then
                KeyAscii = 0
            End If
        Case Asc(".")
            If InStr(1, ctrl.value, ".") > 0 Then
                KeyAscii = 0
            End If
        Case Else
            KeyAscii = 0
    End Select
      
    End Function
    

     

    Then in the Forms TextBox_keypress event you can use the code like this, for example:

    Private Sub Text2_KeyPress(KeyAscii As Integer)
    
    ChckOnlyNumbers KeyAscii
    
    End Sub
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishaniModerator Monday, December 26, 2011 4:56 PM
    • Marked as answer by Admin-Dev Wednesday, January 4, 2012 9:38 PM
    Monday, December 26, 2011 4:55 PM
    Moderator

All replies

  • First of all, when creating a Function, declare it as a Function. Then also you need to change the variable Form_Name.[TextBox1] to strFieldName for example. So your Function will be like this below (untested):

     

     

    Public Function LimitTextBox1(strFieldName As String)
        Select Case KeyAscii
            Case Asc("0") To Asc("9")
            Case Asc("-")
                If InStr(1,strFieldName, "-") > 0 Or strFieldName.SelStart > 0 Then
                    KeyAscii = 0
                End If
            Case Asc(".")
            If InStr(1, strFieldName, ".") > 0 Then
                KeyAscii = 0
                End If
            Case Else
                KeyAscii = 0
        End Select
    End Function
    

     

     

    Then to use it in your Form, call it like this:

     

    Public Sub TextBox1_KeyPress(KeyAscii As Integer)
        Call LimitTextBox1(TextBox1)
    End Sub
    

     

    You might be interested to create inputmask/ validation rule for your Text Box instead. See below example:

    http://support.microsoft.com/kb/311171

    http://support.microsoft.com/kb/311172

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, December 23, 2011 6:25 PM
    Moderator
  • Hi,

    I receive error message if I use your code:

    Type mismatch error and points to  strFieldName.SelStart > 0 Then

    Friday, December 23, 2011 7:31 PM
  • My bad, try to dim the variable as Control, as your using Control properties, so the code will be like this:

     

    Public Function LimitTextBox1(ctrl As Control)
        Select Case KeyAscii
            Case Asc("0") To Asc("9")
            Case Asc("-")
                If InStr(1,ctrl.Value, "-") > 0 Or ctrl.SelStart > 0 Then
                    KeyAscii = 0
                End If
            Case Asc(".")
            If InStr(1, ctrl.Value, ".") > 0 Then
                KeyAscii = 0
                End If
            Case Else
                KeyAscii = 0
        End Select
    End Function
    



    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, December 23, 2011 9:14 PM
    Moderator
  • Ok,

    I made the change, no erro message, but nothing happens also, I can insert char in the textbox

    Regards

    Friday, December 23, 2011 9:37 PM
  • Ok, sorry for the late response, did not had the time to fully test the function.

    Here the latest small correction to get the code working in your Forms TextBox _keypress event.

     

    Modify the Function to below code:

    Public Function ChckOnlyNumbers(KeyAscii As Integer)
    
    Dim ctrl As Control
    Set ctrl = Screen.ActiveControl
    
    Select Case KeyAscii
        Case Asc("0") To Asc("9")
        Case Asc("-")
            If InStr(1, ctrl.value, "-") > 0 Or ctrl.SelStart > 0 Then
                KeyAscii = 0
            End If
        Case Asc(".")
            If InStr(1, ctrl.value, ".") > 0 Then
                KeyAscii = 0
            End If
        Case Else
            KeyAscii = 0
    End Select
      
    End Function
    

     

    Then in the Forms TextBox_keypress event you can use the code like this, for example:

    Private Sub Text2_KeyPress(KeyAscii As Integer)
    
    ChckOnlyNumbers KeyAscii
    
    End Sub
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishaniModerator Monday, December 26, 2011 4:56 PM
    • Marked as answer by Admin-Dev Wednesday, January 4, 2012 9:38 PM
    Monday, December 26, 2011 4:55 PM
    Moderator
  • It worked!

     

    Thank you!

    Wednesday, January 4, 2012 9:38 PM