none
ACC2000: problem with validation of alphanumeric into textbox

    Question

  • I have rather specific problem with validation input in textbox. I want to provide the ability to insert alphanumeric value into textbox. For example:

    Case 1: Null value.

    Case 2: 1000 or some other integer value...

    Case 3: 2387p or 321n or 365m or 6877k.....

     

    Suffixes «p» or «n» or «m» or «k» stand for:

     

    pico: 10e-12

    nano: 10e-9

    milli: 10e-3

    kilo: 10e3

     

    In other words, 365m means 364milli(grams), 6877kilo(grams)... etc.

     

    Problem arises when I insert such alphanumeric value into textbox. I tried following code in both beforeupdate and afterupdate events, various error messages were shown in runtime....(((( Sometimes it works, mostly erratically....

     

    *************************************************


    Private Sub Text387_AfterUpdate()

       

    Dim Alpha As Variant

    Dim LeftPartAlpha As Integer

    Dim RightPartAlpha As String

     

    alpha=text387.text

        If Not IsNull(Text387.Value) Then

            If Not IsNumeric(Text387.Value) Then

               

                MsgBox "length: " & Len(alpha)

                MsgBox "rightpart: " & Right(alpha, 1)

                MsgBox "leftpart: " & Left(alpha, Len(alpha) - 1)

               

                If IsNumeric(Left(alpha, Len(alpha) - 1)) Then

                    'Text387.Value = Left(Text387.Value, Len(Text387.Value) - 1)

                    Exit Sub

                End If

                          

            End If

        End If

    End Sub

     

    ***********************************************

     

    In fact, I plan to separate left part of alphanumeric into integer variable, and right part of alphanumeric into string variable. In further branching I want to perform numeric conversion based on string variable but mathematics is not an issue. Problem is error handling – Access explicitly says alphanumeric input is not allowed (both in beforeupdate and afterupdate event) because underlying table field is numeric (Single)....

     

    I do not have any new idea how to solve this issue based on my Access knowledge....)))

    Environment is WinXP and Access 2000.

    Any advices are welcome.

    TIA, Zare

    Thursday, December 19, 2013 6:40 PM

Answers

  • This would be a textbox bound to a numeric field, right?

    That is the problem.  You will have to do work with this a bit because you are getting a mix of data.  So I would suggest you make this textbox unbound and use it for data entry and to do the math.  then have an hidden textbox bound the the numeric field where you put the result of the math.  One other thing.  You are doing this in the After Update event, but it should be in the Before Update.  Then use the After Update event to populate the hidden textbox.

    Private Sub Text387_BeforeUpdate(Cancel As Integer)
    Dim Alpha As String
    Dim LeftPartAlpha As Integer
    Dim RightPartAlpha As String
    Dim MathResults As Single

     alpha=Me.text387

     If IsNull(Me.Text387) Then
      Cancel = True
      Exit Sub
     End If
     
     If Not IsNumeric(Right(Me.Text387,1) then
      RightPartAlpah = Right(Me.Text387,1)
      LeftPartAlpha = Cint(Replace(Me.txt387),RightPartAlpha,vbNullString)
     End if

    '************************
    ' Do Your Math Here
    '************************
      Me.txt387 = MathResults

    End Sub

    And in the After Update Event:

    Private Sub Text387_AfterUpdate()

     Me.HiddenTextBox = Me.Txt387
     
    End Sub

    Two other suggestions:

    1. Change your Single to a Double. Given the data you are using it would not surprise me to see you get an overflow or a truncation of the values.

    2. Text387 means nothing. Use names the give you an idea of what the control is for. It makes it easier on you and it keeps anyone who is working on the application after you from calling you names :)

    Thursday, December 19, 2013 7:48 PM
  • Hi TIA,

    As dhargis mentioned you can use a unbound textbox to let user input the value and caluate the value in beforeUpdate event. At last save the caluated value to the bound textbox which hide to user. There is some typo and you can refer to code below to achieve the goal. Please remove the after Update event. The alphanumeric is a bound textbox.

    Private Sub Text387_BeforeUpdate(Cancel As Integer)
    Dim Alpha As String
     Dim LeftPartAlpha As Integer
     Dim RightPartAlpha As String
     Dim MathResults As Single
    
     Alpha = Me.Text387
    
     If IsNull(Me.Text387) Then
       Cancel = True
       Exit Sub
      End If
      
      If Not IsNumeric(Right(Me.Text387, 1)) Then
       RightPartAlpha = Right(Me.Text387, 1)
       LeftPartAlpha = CInt(Replace(Me.Text387, RightPartAlpha, vbNullString))
      End If
    
    '************************
     ' Do Your Math Here
     '************************
     Me.alphanumeric = MathResults
    End Sub

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 26, 2013 3:15 AM

All replies

  • This would be a textbox bound to a numeric field, right?

    That is the problem.  You will have to do work with this a bit because you are getting a mix of data.  So I would suggest you make this textbox unbound and use it for data entry and to do the math.  then have an hidden textbox bound the the numeric field where you put the result of the math.  One other thing.  You are doing this in the After Update event, but it should be in the Before Update.  Then use the After Update event to populate the hidden textbox.

    Private Sub Text387_BeforeUpdate(Cancel As Integer)
    Dim Alpha As String
    Dim LeftPartAlpha As Integer
    Dim RightPartAlpha As String
    Dim MathResults As Single

     alpha=Me.text387

     If IsNull(Me.Text387) Then
      Cancel = True
      Exit Sub
     End If
     
     If Not IsNumeric(Right(Me.Text387,1) then
      RightPartAlpah = Right(Me.Text387,1)
      LeftPartAlpha = Cint(Replace(Me.txt387),RightPartAlpha,vbNullString)
     End if

    '************************
    ' Do Your Math Here
    '************************
      Me.txt387 = MathResults

    End Sub

    And in the After Update Event:

    Private Sub Text387_AfterUpdate()

     Me.HiddenTextBox = Me.Txt387
     
    End Sub

    Two other suggestions:

    1. Change your Single to a Double. Given the data you are using it would not surprise me to see you get an overflow or a truncation of the values.

    2. Text387 means nothing. Use names the give you an idea of what the control is for. It makes it easier on you and it keeps anyone who is working on the application after you from calling you names :)

    Thursday, December 19, 2013 7:48 PM
  • Hi TIA,

    As dhargis mentioned you can use a unbound textbox to let user input the value and caluate the value in beforeUpdate event. At last save the caluated value to the bound textbox which hide to user. There is some typo and you can refer to code below to achieve the goal. Please remove the after Update event. The alphanumeric is a bound textbox.

    Private Sub Text387_BeforeUpdate(Cancel As Integer)
    Dim Alpha As String
     Dim LeftPartAlpha As Integer
     Dim RightPartAlpha As String
     Dim MathResults As Single
    
     Alpha = Me.Text387
    
     If IsNull(Me.Text387) Then
       Cancel = True
       Exit Sub
      End If
      
      If Not IsNumeric(Right(Me.Text387, 1)) Then
       RightPartAlpha = Right(Me.Text387, 1)
       LeftPartAlpha = CInt(Replace(Me.Text387, RightPartAlpha, vbNullString))
      End If
    
    '************************
     ' Do Your Math Here
     '************************
     Me.alphanumeric = MathResults
    End Sub

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 26, 2013 3:15 AM