Restricting Single Quote Symbol from being typed in Text Box 1

[cdw0308]

Is there a way to restrict the single quote symbol from being typed into a text box on a form?
I am running into errors on my append and update statments when a user types in a description such as " 25' Tape Measure "
When I remove the single quote the statements process just fine.

I really appreciate any help on this
Thanks.

 

[SeeThru]

You could search for it on onUpdate, and replace it with somehting else, or remove it.

Function FindSpecial(Field, ANSICode, ReplaceWith As String) As Variant
'Ansi Codes: 39= single quote

'to use, create an update query, with each field updating itself, ie
'Field1 UPDATE TO =FindSpecial([Field1],39,", ")
Dim x As Integer, y As Integer, strText As String, FindWhat As Variant, StrLength As Integer
Dim FindLen As Integer
FindWhat = ANSICode
FindLen = 1 'can only handle single characters at the moment
x = 1
If IsNull(Field) Then GoTo Find_Exit
strText = Field
If InStr(1, strText, Chr(FindWhat), vbBinaryCompare) Then 'is the special present?
StrLength = Len(strText)
    Do Until x = 0
        x = InStr(1, strText, Chr(FindWhat), vbBinaryCompare) 'find location
        If x > 0 And Not IsNull(x) Then
            strText = Left(strText, x - 1) & ReplaceWith & Mid(strText, x + FindLen, StrLength)
            y = InStr(1, strText, "  ", vbBinaryCompare) 'check for double spaces created by replace
            If y > 0 And Not IsNull(y) Then
                StrLength = Len(strText)
                strText = Left(strText, y - 1) & " " & Mid(strText, y + 2, StrLength)
            End If
        End If
    Loop 'check for more specials until x=0 (not found)
End If
If Trim(strText) = "" Then
    FindSpecial = Null
Else
FindSpecial = Trim(strText)
End If
Exit Function

Find_Exit:
'undoes it all
FindSpecial = Field
End Function

 

[PHV]

I'd use the Replace function when building the SQl code:

strValue = " 25' Tape Measure "
strSQL = "UPDATE myTable SET myField='" & Replace(strValue, "'", "''") & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

In Design View goto your form's Properties -- Events and set KeyPreview to Yes. Then enter this code:

Private Sub YourTextBox_KeyPress(KeyAscii As Integer)
If KeyAscii = 39 Then KeyAscii = 0
End Sub

Now nothing will happen when the single quote key is pressed while in this textbox.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PHV]

But, why restrict the user just because the programmer is too lazy to properly code its stuff ?

 

[CautionMP]

...programmer is too lazy...

Lazy? More like covering the bases.

Doing it in the GUI let's the user know that they cannot use the single quote in the field (don't you hate it when you type something in a field and later find out it had been manipulated behind the scenes?)

The code missinglinq provided would not capture the character being pasted into the field from the clipboard and this would be covered by the solutions provided by PHV or SeeThru.

Just my two cents,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[missinglinq]

Excuse me, I thought I was answering the OP's question:

Is there a way to restrict the single quote symbol from being typed into a text box on a form?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PHV]

And I answered the following (without frustating the user ...)

I am running into errors on my append and update statments