Two Text Entry Questions

[MarkA70]

Two Text Entry Questions Solved

I help with a database for a small Non Profit, we do not get computer literate volunteers, good folks, just intimidated. Need help with two problems on data entry.

1. How to strip a blank space out of a text box. That is they type in a name, but instead of Sam, it is <Space> Sam. Of course this causes a sort on names to be skewed with the <Space> first names at the head of the list. Need to remove this <Space>.

2. Some how, don't ask me how, they will get non text characters in a name, like Wi!!iam for William, how that it is possible to get two bangs in there I dunno. However, I need to be able to catch that on an after update or on exit event to send them back to correct. How do I check for only text characters in a name?

Thanks

 

[jdraw]

Mark,

Here is a sample routine that mocks up part of what you are doing.
It takes a string of characters s (simulates your textbox), and
1) Trim(s) removes leading and trailing spaces
2) Looks at each character to ensure it is a thru Z (alphabetic)
a) if not alpha it gives a message and stops
b) if all characters are alphabetic it gives a Msg "acceptable"

You could adapt this and put your code in the before update event of the textbox, or the before update of the form.

Sub testAlp()
    Dim s As String: s = "  Wi!!iam "
    s = Trim(s)                     'remove leading and/or trailing spaces
    Dim i As Integer: i = 0         '
              
    For i = 1 To Len(s)              'check each character for alphabetic
        If Not (Mid(s, i, 1) Like "[a-Z]") Then
            MsgBox Mid(s, i, 1) & " is not a valid alpha character ", vbOKOnly
            Exit Sub
        End If
    Next i
    MsgBox "This is acceptable " & s
End Sub

Good luck.

 

[JHB]

1.
Create and run an update query.
2.
You can do a test when a key is pressed, using the key press event, ( KeyPress(KeyAscii As Integer)).

  If InStr(1, "ABCDESGHIJKLMNOPQRSTUVWZX", Chr(KeyAscii)) = 0 Then
    MsgBox ("Not valid")
    KeyAscii = 0
  End If

 

[MarkA70]

Thank you both, very insightful coding. Both solutions are very good, and this will help A LOT!