Query Throws Error Because Of Punctuation In Search Arguement

[VSCurtis]

Hi Guys,

Could use a little insight here. I have a procedure that is called in my Card Name After Update Event. The procedure attempts to check to see if the card name that I just typed in is already in the database, in other words, a duplicate card. If the procedure gets a hit then it throws up a message box telling me that a card already exists with that name and gives me the chance to delete the record and the card. I just threw something at the query that it had trouble processing probably because of punctuation in the card name. The card name is The High Priestess' Staff. My question is how to code my SQL statement to account for stuff like this. My guess is that the query is looking for the closing single quote and there isn't one, so it throws an error.

Here's the code

Private Sub CheckForDuplicates()
Dim sSQL As String
Dim rsCardsDuplicates As Recordset
sSQL = "SELECT tblCards.[Card Name]FROM tblCards WHERE tblCards.[Card Name] = '" & txtCardName.Value & "';"
If rsCards.EditMode = dbEditAdd Or rsCards.EditMode = dbEditInProgress Then
   Set rsCardsDuplicates = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
   If rsCardsDuplicates.RecordCount > 0 Then
      DeleteRecord (True)
   End If
   rsCardsDuplicates.Close
   If Not rsCardsDuplicates Is Nothing Then
      Set rsCardsDuplicates = Nothing
   End If
End If
End Sub

Any help you can provide is appreciated

[CJ_London]

if you are referring to the single quote, you need to replace it with two single quotes e.g.

WHERE tblCards.[Card Name] = '" & replace(txtCardName,"'","''") & "';"

Note you do not need .value which is the default property

[VSCurtis]

Thanks for the assist. I'll give it a shot. As for my use of .value, I like to fully qualify my code. I'm aware that it's not needed

[VSCurtis]

I tried what you suggested. The query string still throws the same error. Here's the modified code.

Private Sub CheckForDuplicates()
Dim sSQL As String
Dim rsCardsDuplicates As Recordset
sSQL = "SELECT tblCards.[Card Name]FROM tblCards WHERE tblCards.[Card Name] = " & Replace(txtCardName, "'", "''") & "';"
If rsCards.EditMode = dbEditAdd Or rsCards.EditMode = dbEditInProgress Then
   Set rsCardsDuplicates = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
   If rsCardsDuplicates.RecordCount > 0 Then
      DeleteRecord (True)
   End If
   rsCardsDuplicates.Close
   If Not rsCardsDuplicates Is Nothing Then
      Set rsCardsDuplicates = Nothing
   End If
End If
End Sub

[davegri]

Private Sub CheckForDuplicates()
Dim sSQL As String
Dim rsCardsDuplicates As DAO.Recordset
dim sWork as string
sWork = replace(txtCardName,"'","''") 
sSQL = "SELECT tblCards.[Card Name]FROM tblCards WHERE tblCards.[Card Name] = '" & sWork & "';"
debug.print sSQL
If rsCards.EditMode = dbEditAdd Or rsCards.EditMode = dbEditInProgress Then
   Set rsCardsDuplicates = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
   If rsCardsDuplicates.RecordCount > 0 Then
      DeleteRecord (True)
   End If
   rsCardsDuplicates.Close
   If Not rsCardsDuplicates Is Nothing Then
      Set rsCardsDuplicates = Nothing
   End If
End If
End Sub

If this doesn't work, tell us what the error message is, and what line the code stops on.

[VSCurtis]

I think I got it figured out. I used the multiple quotes method to avoid having the apostrophe treated as a delimiter. This is my fix. It should work. Haven't tested it yet. I had to rollback to a backup database because the error caused some other issues that were just easier to deal with by starting from scratch.

Private Sub CheckForDuplicates()
Dim sSQL As String
Dim rsCardsDuplicates As Recordset
sSQL = "SELECT tblCards.[Card Name]FROM tblCards WHERE tblCards.[Card Name] = """ & txtCardName.Value & """" & ";"
If rsCards.EditMode = dbEditAdd Or rsCards.EditMode = dbEditInProgress Then
   Set rsCardsDuplicates = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
   If rsCardsDuplicates.RecordCount > 0 Then
      DeleteRecord (True)
   End If
   rsCardsDuplicates.Close
   If Not rsCardsDuplicates Is Nothing Then
      Set rsCardsDuplicates = Nothing
   End If
End If
End Sub

[ssanfu]

As davegri pointed out, you should disambiguate which object model you are using:

Dim rsCardsDuplicates As DAO.Recordset

[VSCurtis]

My fix worked. The apostrophe was handled correctly. Not a problem that happens very often and most of the time using single ticks works just fine, but given the nature of some of these card names I needed to be able to enclose my search string in quotes not single ticks because punctuation does appear in some of the card names and should be treated as such. I'm a little rusty in dealing with some of this stuff and I appreciate your patience and assistance.