asked on

Single Quote In Key Problem

Hi Folks,
In MS Access 2003 when I run the code below (part of a big chunk of code) it has a problem with the data in the following field when the field contains a single quote character. e.g. (')
Dim PARM_ADDRESS_KEY As String

Data Example:
CDNL7M 0P9***5483 WALKER'S LINE**********************BURLINGTON ON L7M 0P9******************* *******************************************************************

'MsgBox "Step 1110 - Update With Member Home Address Data."
On Error Resume Next 'Mandatory to stop BOF / EOF Error.
Dim RS_10_MEMBER_MSTR_1110 As New ADODB.Recordset
Dim Cmd_MEMBER_HOME_ADDRESS_KEY As String
Cmd_MEMBER_HOME_ADDRESS_KEY = "SELECT HOME_ADDRESS_KEY, SYS_MEMBER_KEY, SYS_MEMBER_LKUP " & _
"FROM 10_MEMBER_MSTR " & _
"WHERE HOME_ADDRESS_KEY = '" & PARM_ADDRESS_KEY & "';"
RS_10_MEMBER_MSTR_1110.Open Cmd_MEMBER_HOME_ADDRESS_KEY, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
RS_10_MEMBER_MSTR_1110.MoveFirst
While RS_10_MEMBER_MSTR_1110.EOF = False
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006.AddNew
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_KEY = [PARM_ADDRESS_KEY]
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!POSTAL_CODE = [WRK_POSTAL_CODE]
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_USE_TYPE = "H01"
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_TYPE_SEQ = "A01"
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_SOURCE_KEY = RS_10_MEMBER_MSTR_1110!SYS_MEMBER_KEY
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_SOURCE_CONTEXT = "Member - Home Address 01"
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_SOURCE_INFO = RS_10_MEMBER_MSTR_1110!SYS_MEMBER_LKUP
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_USE_TABLE = "10_MEMBER_MSTR"
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_USE_FIELD = "HOME_ADDRESS_KEY"
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!BASE_REC = [WRK_BASE_REC]
RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006.Update
[ADDRESS_USE_COUNTER] = [ADDRESS_USE_COUNTER] + 1
RS_10_MEMBER_MSTR_1110.MoveNext
Wend
RS_10_MEMBER_MSTR_1110.Close

How can I eliminate the problem of the single quote being in a field that is used for a keyed lookup?

Thanks.
Bob Collison.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Collison

ASKER

Hi Angel III,
Thanks for the solution.

I have a problem with understanding syntax so could you please provide the code for my particular case for each of your solutions?

Thanks.
Bob Colison

Bob Collison

ASKER

Hi Angel III,
I tried the first two solutions with the following code and both didn't solve the problem.
"WHERE HOME_ADDRESS_KEY = " & Chr$(34) & PARM_ADDRESS_KEY & Chr$(34)
"WHERE HOME_ADDRESS_KEY = '" & Replace(PARM_ADDRESS_KEY, "'", "''") & "' "

Have I done something wrong?

I don't have any idea what you mean by * use a parametrized query so will wait for a further update from you.

Thanks. Bob Collison

Bob Collison

ASKER

Hi Angel III,

With reference to my last update. I didn't make the change to all of the occurrences of the code. I have since done that and both Solutions 1 and 2 work.

Sorry for the misleading update.

I am still interested in the syntax for Solution 3 as you have suggested that it is the best one.

Thanks. Bob Collison.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Collison

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for Bob_Collison's comment #a39874642

for the following reason:

Multiple solutions were provided all of which are applicable in different situations.
Quick response and great solutions.

Guy Hengel [angelIII / a3]

if my suggestions worked, please don't close with your own comment, but with mine
thanks

Bob Collison

ASKER

Fast response and great solution!