Full Version: Disallowing The Use Of A Specific Special Character
UtterAccess Forums > Microsoft® Access > Access Forms
Good morning! I need to write a function that will trap the use of a specific special character; in this case, the single quote (" ' ").
Here is the code that I have managed to hack around in to this point. It obviously isn't what I need. Some assistance would be greatly appreciated!
Private Sub txtSolicitDescrTitle_AfterUpdate()
Dim LPos As Integer
Dim LChar As String
Dim LInValid_Values As String
Dim strLength As Integer
'*Make sure user is NOT using a single quote in Title*
    LChar = Me.txtSolicitDescrTitle.Value
    strLength = Len(LChar)
    'Start at first character in pValue
    LPos = 1
    'Set up values that are considered to be alphanumeric
    LInValid_Values = "'"
    'Test each character in pValue
    While LPos <= strLength
        'Single character in pValue
        LChar = Mid(pValue, LPos, 1)
        'If character is not alphanumeric, return FALSE
        If InStr(LInValid_Values, LChar) = 0 Then
            'AlphaNumeric = False
            MsgBox ("bad")
        End If
        'Increment counter
        LPos = LPos + 1
End Sub

Thanks very much!!
If you want to do this through an event, I would suggest the BeforeUpdate event rather than the AfterUpdate event. However, you can likely put this as a validation rule to simplify things for you... I'm not sure the exact syntax, but "Like *'*" will tell if there's an aposhtrophe in there.
Another method would be to capture it as the user types... get the last character of the .Text property in the Change event of the control, and check to see if it's an apostrophe, warn the user, etc etc.
does that help at all?
Doug Steele
The AfterUpdate event would seem to be too late: you want the BeforeUpdate event. And the built-in InStr function is a lot simpler than looping through the data:
Private Sub txtSolicitDescrTitle_BeforeUpdate(Cancel As Integer)
  If InStr(Me!txtSolicitDescrTitle, "'") > 0 Then
    MsgBox "Please do not use single quotes."
    Cancel = True
  End If
End Sub

Out of curiosity, why are you keeping them from using single quotes?
Leave it to me to under-simplify something! doh.gif
Thanks very much for the help, guys!
Doug, it's a client requirement, for some reason. Who am I to argue, especially since the check already cleared.
- Keith
Doug Steele
Glad it's working for you.
If it had been to avoid problems with searching for strings with single quotes in them, I was going to point you to suggestions for how to avoid those problems, but sounds as though that's not necessary.
Farbeit from me to turn down an opportunity to learn something new on this fine Friday morning!! If you some links you can share, I can file them with my other reference material. Thanks!!!
Doug Steele
Being self-serving (), I'll point you to a couple of my Access Answers columns from Pinnacle Publication's "Smart Access".
Shortcuts, Quotes and What Have I Done?
Embedded quotes in SQL statements
You may consider using KeyPress event to prevent the user from even being able to think the character will be used. Somthing like this:
'In a standard module I have the following constants
Public Const conAppInvalidCharacters = "'\/*?""<>|"
Public Const conAppCharacterSubstitute = "~"
'This would be in the form's code
Private Sub someTextBoxControlName_KeyPress(KeyAscii As Integer)
    If InStr(1, conAppInvalidCharacters, Chr(KeyAscii), vbTextCompare) Then
        KeyAscii = Asc(conAppCharacterSubstitute)
    End If
End Sub

Also ... if values can be pasted into your text box, the Change event can also useful.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.