TextBox Value start with characters letter/alphabet

[Niel HS]

My Form has 6 Text Box fields and just 1 of them need to set the correct value format.

I just want to set it to make the prefix value to be as alphabet otherwise it will prompt message Invalid.

I’ve try to use below code, but it just to allow first character to be as alphabet can’t vary, and not allow to put dash (-).

Private Sub SerialNumber_KeyPress(KeyAscii As Integer)
On Error GoTo SerialNumber_KeyPress_Error

If KeyAscii <> 8 Then 'Checks if Backspace key was pressed
Me.SerialNumber.SelStart = Len(Me.SerialNumber.Text) 'This makes sure that characters
'are entered in order (A00000)
If Len(Me.SerialNumber.Text) < 1 Then
If (KeyAscii < 97 Or KeyAscii > 122) And _
(KeyAscii < 65 Or KeyAscii > 90) Then 'Ensures that First
KeyAscii = 0 'Character is A through Z
Else
If (KeyAscii >= 97 And KeyAscii <= 122) Then
KeyAscii = KeyAscii - 32 ' Capitalize the first charater
End If

End If
Else ' If Keypress is not the First Character and only allows numbers
If (KeyAscii >= 48 And KeyAscii <= 57) Then 'Only allows numbers after
KeyAscii = KeyAscii 'First Letter
Else
KeyAscii = 0
End If
End If

End If
On Error GoTo 0
Exit Sub

SerialNumber_KeyPress_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SerialNumber_KeyPress of " & Me.Name
End Sub

I'm glad if someone can help me on this...Thanks !!

 

[Galaxiom]

Probably better to use the AfterUpdate Event to check the finished value rather than catching every keystroke.
Get the first character with Left()

Uppercase would be simpler done with UCase()

If you have a fixed number of characters you could consider an Input Mask.

Another alternative is the Validation Rule.

 

[Niel HS]

Yeah...Input Mask is only work for the Fixed Format Value.

But the value which need to input are vary, the important thing is the value must start with character alpha the can be follow with any other characters.

I'm glad if someone can help with some code to handle this situation. Thanks !!

 

[Galaxiom]

You are basically on the right track but as I said earlier it would be simpler if you worked with the AfterUpdate Event rather than running code on every keystroke.

 

[DCrake]

I would be tempted to split the controls into two seperate ones and concatenate them after. That way you only need to worry about one data type per control.

 

[missinglinq]

Actually, I'd use the BeforeUpdate event for validation, because you can set Cancel = True and focus will stay in the textbox, then use AfterUpdate to insure that the alpha character(s) are in Upper Case.

Private Sub SerialNumber_BeforeUpdate(Cancel As Integer)
If IsNumeric(Left(Me.SerialNumber, 1)) Then
 MsgBox "The Serial Number Must Start With a Letter!"
 Cancel = True
 Me.SerialNumber.SelStart = 0
End If
End Sub

Private Sub SerialNumber_AfterUpdate()
 Me.SerialNumber = UCase(Me.SerialNumber)
End Sub

Linq ;0)>

 

[Galaxiom]

If IsNumeric(Left(Me.SerialNumber, 1)) Then

Testing with IsNumeric would still allow non-alpha characters so I am pretty sure the Ascii test is the only way.

 

[Niel HS]

Wow...just have a time right now...and i have try the code which given by missinglinq that's worked ...awesome..thanks guys.

 

[stopher]

Testing with IsNumeric would still allow non-alpha characters so I am pretty sure the Ascii test is the only way.

I agree. For example, Missinglinq's code would allow % or $ as the first character.

I suggest this:

Dim s As String
s = Left(Me.SerialNumber, 1)
If Asc(UCase(s)) = Asc(LCase(s)) Then
 MsgBox "The Serial Number Must Start With a Letter!"
 Cancel = True
 Me.SerialNumber.SelStart = 0
End If

This works because Lcase and Ucase only have any effect on letters. So we compare the lower case and an upper case values. If they are different then the character must be a letter.

Chris

 

[Niel HS]

I've just try the code ==> That's Perfect Code...its block all the non-alpha characters in the first digit.

Thanks so much to you stopher. You're the helper, GooD JoB !!

 

[Galaxiom]

What Chris posts inside the box always shows he is thinking outside of the box.

 

[stopher]

What Chris posts inside the box always shows he is thinking outside of the box.

How about an abstract solution:

If Abs(Abs(Asc(s) - 93.5) - 16) > 12.5 Then    'it's not a letter

The point being we are test for two ascii ranges:
65 to 90 and 97 to 122

If we subract 93.5 then our ranges become:
-28.5 to -3.5 and 3.5 to 28.5

If we then take the absolute value then we only need to consider the range:
3.5 to 28.5

If we then subtract 16, the range for consideration is:
-12.5 to 12.5

So taking the ABS again our range is:
0 to 12.5

So anything with a result from our expression of greater than 12.5 is not a letter.