DAO - apostrophe in primary key

  • Thread starter Thread starter mscertified
  • Start date Start date
M

mscertified

I'm using DAO to process a recordset, I build SQL using
replace(MyName,"'","''") because primary key contains names which may have
apostrophes.
I do a FINDFIRST looking for the key, I get a NOMATCH on the record even
though it is there and then my add fails with duplicate index.
How should this be handled?
Here is my code:

strFN = rs1.Fields("FirstName").Value
strLN = rs1.Fields("LastName").Value
strTel = rs1.Fields("PubPhoneNumber").Value
strCC = strLN & Left$(strFN, 1) & Right$(strTel, 4)
strSQL = "ClientCode = '" & Replace(strCC, "'", "''") & "'"
rs2.FindFirst strSQL
If rs2.NoMatch Then
' Add new record
With rs2
.AddNew
.Fields("ClientCode").Value = strCC
............
.Update <========= fails with dup key
End With
Else
 
I'm no expert, but it seems like it shoul be;

strFN = rs1.Fields("FirstName").Value
strLN = rs1.Fields("LastName").Value
strTel = rs1.Fields("PubPhoneNumber").Value
strCC = Replace(strLN, "'", "") & Left$(strFN, 1) & Right$(strTel, 4)
strSQL = "ClientCode = '" & strCC & "'"
rs2.FindFirst strSQL
If rs2.NoMatch Then
' Add new record
With rs2
.AddNew
.Fields("ClientCode").Value = strCC
............
.Update
End With
Else
 
That should work.

However, since strCC should never contain a double quote, you could try
using this instead:

strSQL = "ClientCode = """ & strCC & """"

(that's three double quotes in front, and four double quotes after)
 
Still get dup key on the .UPDATE
from immediate window:
?strsql
ClientCode = "D''BourgetL5465"
?strcc
D'BourgetL5465

This key is in the table so it should not have hit the NOMATCH condition and
not be trying to add a new record. Thuis column is not the primary key (which
is an autonumber), its a unique index.


Any more ideas?
 
It works if I use your technique and get rid of the replace. It seems there
is no need to double the single quotes.
 
Back
Top