app bombs when appostrophe in text 2

[Corinne]

What is the correct way to program when filling in grids or text boxes with information that may contain an appostrophe? I have a work around which is just a function:

Dim tmp As String, pos As Integer
Dim i As Integer, char As String
For i = 1 To Len(Targetstring)
char = Mid(Targetstring, i, 1)
tmp = tmp + char
If char = "'" Then tmp = tmp + "'"
Next i
funAppendAppostrophe = tmp

I was wondering if there was a better way to handle this problem. I'm having problems saving data that contains an appostrophe back to a table using results from a recordset. Any suggestions?

Thanks,
Corinne

 

[Hackster]

You could try this with your grid providing the grid as a KeyPress event. I put this one liner in the keypress event of every text box, on every screen I have, and never have a problem with single quotes:

If KeyAscii = 39 Then KeyAscii = 96

Ascii 39 is the ' character. Each time the user types that character in the text box, it automatically, and instantly becomes changed to ` (KeyAscii 96) It looks almost identical, both on screens and on reports. I've been doing this little switch-a-roo for years and no one has ever said a word.

 

[Niv3k]

If you do not want the apostrophe at all, then Hacksters suggestion is good, except I have done roughly the same thing in the change event of the textbox, in case someone pastes text inside it. But if you do not want it because it is interfering with a SQL Update or Insert statement Then you can wait until when you actually do the update and replace it with two (2) single apostrophes. For example: UPDATE Table SET Field='O''Connor'.

Do this with a REPLACE function:

NewStr = Replace (oldStr, "'", "''")[\code]

Kevin

 

[Hackster]

Kevin makes a VERY good point about people pasting code in the text box (which, of course, would not cause the keypress event to fire). To absolutely assure that apostrohpes don't get written back in an SQL statement, you would need some code in both Change and KeyPress.

 

[Corinne]

What type of code? Do you have any expmples?

 

[Hackster]

I just whipped this up off the top of my head and ran it through one test, and it seemed to work. You will probably need to play with it a little bit, but I think this should get you going:

Dim TempString As String
Dim SpotInString As Integer
Dim MyString As String
'Search for ' character
If (InStr(MyString, "'&quot > 0) Then
TempString = MyString
SpotInString = InStr(TempString, "'&quot
'If found, replace it with `
While (SpotInString > 0)
TempString = Left$(TempString, SpotInString - 1) & Chr$(96) & Right$(TempString, Len(TempString) - SpotInString)
SpotInString = InStr(TempString, "'&quot
Wend
End If

 

[Niv3k]

The replace function makes things so much easier, though... I don't know which is *faster*, but the Replace is much less code... If you have a form and a textbox, put this code in the Text1_Change event

Private Sub Text1_Change()
Dim iPos As Integer
    iPos = Text1.SelStart
    Text1.Text = Replace (Text1.Text, "'", Chr(34)) 
    Text1.SelStart = iPos
End Sub

If you type an apostrophe, the Change event replaces any apostrophes. It fires twice, once when the contents are changed by the user, and once when the code changes the contents. But if there are no apostrophes, the contents are not changed so the change event only fires once.
The .SelStart is there to remember the cursor position and put it back where it belongs, because after the contents change, .SelStart = 0 automatically.

 

[Hackster]

Kevin: Way Cool! I like it. I don't believe I've ever used Replace before, although I've seen it in many, many examples.

Carmilita: I ran his example in the change event of a text box, and it worked like a champ. If you whip this code into the change event, and use my one liner in the Keypress event, you should not have any more heartburn with apostrophes. (I changed his Chr(34) to Chr(96) which I like better. What you change the apostrophe to, however, is your call, but for the change event, this is the code to use.)

 

[Niv3k]

Well, I only used the " (34) because I wanted a visible difference when you hit apostrophe, and when you change the key to ` (96) you can't see much difference.

Kevin

 

[Hackster]

I use 96 is for that very reason. It is practically transparent to the user.

 

[Corinne]

Thank you all for your suggestions & direction. I now have several directions to explore. Thanks again.

Corinne

 

[Dzidze]

Here is the coding I've done. I use it in all of my Apps.
I put it in a start up module. Then whenever I need to insert data in my App, i just call it:

Function Apostrophe(rsSQL As String) As String
Dim sSQL As String

Dim nCounter As Integer

On Error GoTo err_apostrophe

For nCounter = 1 To Len(rsSQL)
sSQL = sSQL & IIf(Mid(rsSQL, nCounter, 1) = "'", "''", Mid(rsSQL, nCounter, 1))
Next

Apostrophe = sSQL

Exit Function
err_apostrophe:
MsgBox Error(Err)
End Function

 

[scorpio66]

You can make your life a lot simpler if you avoid doing things like this:

update table set name = value ...
or
insert into table name (fields) values (values)

and instead use

With oRS
.AddNew
!fieldname = value
!fieldname = value

etc

that way quotes etc get handled correctly.

In this instance, value comes from a variable. If you need to insert literal strings which may or may not have quotes, then use replace.