An Apostrophe Is Causing Problems While Inserting!

Hi,

In my database if the user wants to change a record, I make them store the old record in another table. I am using the following code.


Private Sub cmdArchive_Click()

On Error GoTo ErrorHandler
Dim conn As ADODB.Connection
Dim i As Integer
Dim s As String
Dim sSQL As String

sSQL = "INSERT INTO tblNamesArchive ( txtGivenName, txtSurname )" _
& "VALUES ( '" & txtGivenName & "', '" & txtSurname & "');"

Debug.Print sSQL
Set conn = CurrentProject.Connection
conn.Execute sSQL
GoTo ThatsIt
ErrorHandler:
Select Case Err.Number
Case -2147217908 'command text not set
Case -2147217865 'cannot find table
Case 3021 'no records
Case Else
MsgBox "Problem with cmdArchive_Click()" & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description
End Select
ThatsIt:
conn.Close

End Sub


I have no problems making the following code work except if there is an apostrophe " ' " in either the txtGivenName or txtSurname . For example O'Keefe it shows an error but not O"Keefe! How do I fix this?


Thanks

Raghu Prabhu
Melbourne.