Apostrophies (1 Viewer)

tucker61 · Dec 12, 2017

I have apostrophies disabled on my form, but ihave a few instancies now where spell check is putting the apostrohy in the feild.

I have used the following code to disable the apostrophy.

Code:

Private Sub tbComments_KeyPress(KeyAscii As Integer)
                If KeyAscii = 39 Then
                MsgBox "You may not use apostrophes in this field"
                KeyAscii = 0
                End If
End Sub

and the below for the spell check.

Code:

Private Sub tbComments_AfterUpdate()
On Error GoTo Handler

    If Len(Me.tbComments & "") > 0 Then
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdSpelling
        DoCmd.SetWarnings True
    End If

how do i stop the spellcheck suggesting a spelling that contains a apostrophe ? ?

Minty · Dec 12, 2017

I'm guessing this is because the apostrophe upsets some VBA written queries ?

If it is I would suggest changing them to paramaterised queries, it avoids the problem.

Alternatively let the spell check do it's job then simply remove any apostrophes from what it's done.

tucker61 · Dec 12, 2017

it certainly upsets them, it updates the comments into a different table, using the Update Code below.

Code:

mysql = "UPDATE tblQCCharges SET Comments = '" & tbComments & "', Delivery_Reference = '" & tbReference & "' " & _
                "WHERE tblQCCharges.Job_ID=" & Nz(Forms!frmmain.tbJobID, 0) & ";"
CurrentDb.Execute mysql

pbaldy · Dec 12, 2017

Jason's FAQ shows how to handle the apostrophes in text:

http://www.baldyweb.com/BuildSQL.htm

MarkK · Dec 12, 2017

Using code like this you can completely defeat the apostrophe problem...

Code:

Sub Test12934891384()
    Const SQL_UPDATE As String = _
        "UPDATE tblQCCharges " & _
        "SET Comments = p0, " & _
            "Delivery_Reference = p1 " & _
        "WHERE Job_ID = p2;"
        
    With CurrentDb.CreateQueryDef("", SQL_UPDATE)
        .Parameters(0) = Me.tbComments
        .Parameters(1) = Me.tbReference
        .Parameters(2) = Nz(Forms!frmmain.tbJobID, 0)
        .Execute dbFailOnError
        .Close
    End With
End Sub

This creates a temp, parameterized querydef which handles delimiters automatically, and thereby allows apostrophes to be embedded in your data.
hth
Mark

Minty · Dec 12, 2017

That's exaccery what I was suggesting.
Thanks for the example Markk - I was short of time to write one up.

tucker61 · Dec 12, 2017

Thanks for the above, I would of never been able to write that code without the sample you provided for me. Just trying to think where else i can use that now...

Apostrophies (1 Viewer)

tucker61

Registered User.

Minty

AWF VIP

tucker61

Registered User.

pbaldy

Wino Moderator

MarkK

bit cruncher

Minty

AWF VIP

tucker61

Registered User.

Similar threads

Users who are viewing this thread