Code problem, Run-Time error '3075':

[Nicholas]

Hi
I have this piece of code (see below) attached to the
before update event on my 'LocationName' text box, it
checks to see if there is already another record with the
same text in the 'LocationName' field in my table . If
there is a duplicate it deletes the current record. It
works fine until I try to enter a Location Name with an
apostrophe. Eg the name Fred's Tyre Store
Produces the following error

'Run-time error '3075':
Syntax error (missing operator) in query
expression '[LocationName
='Fred's Tyre Store".

Here is a copy of my code

Private Sub LocationName_BeforeUpdate(Cancel As Integer)

If (Not IsNull(DLookup("[LocationName]", _
"tblLocations", "[LocationName] ='" _
& Me!LocationName & "'"))) Then
MsgBox "That Location has already been entered in
the database."
Cancel = True
Me!LocationName.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70
End If
End Sub

If any one can suggest a way to fix this problem,
thanks !

 

[Dirk Goldgar]

Hi
I have this piece of code (see below) attached to the
before update event on my 'LocationName' text box, it
checks to see if there is already another record with the
same text in the 'LocationName' field in my table . If
there is a duplicate it deletes the current record. It
works fine until I try to enter a Location Name with an
apostrophe. Eg the name Fred's Tyre Store
Produces the following error

'Run-time error '3075':
Syntax error (missing operator) in query
expression '[LocationName
='Fred's Tyre Store".

Here is a copy of my code

Private Sub LocationName_BeforeUpdate(Cancel As Integer)

If (Not IsNull(DLookup("[LocationName]", _
"tblLocations", "[LocationName] ='" _
& Me!LocationName & "'"))) Then
MsgBox "That Location has already been entered in
the database."
Cancel = True
Me!LocationName.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70
End If
End Sub

If any one can suggest a way to fix this problem,
thanks !

You can use double-quotes instead of single quotes within your DLookup
criterion to delimit the value, like this:

If (Not IsNull(DLookup("LocationName", _
"tblLocations", "LocationName=" _
& Chr(34) & Me!LocationName & Chr(34)))) _
Then

That will work for cases like "Fred's Tyre Store", but it won't handle
this:

Fred's "Low Price" Tyre Store

because the internal double quotes will now conflict with the delimiting
quotes. To protect against cases like that, you can use the Replace
function (if running Access 2000 or later) to double up the quotes
inside the Location name:

If (Not IsNull(DLookup("LocationName", _
"tblLocations", "LocationName=" _
& Chr(34) & _
Replace(Me!LocationName, """", """""") & _
Chr(34)))) _
Then

Unless I've gotten myself all tangled up in the quotes, that ought to
handle all combinations of single and double quotes in the LocationName
field.