Error 3075

[Frankie via AccessMonster.com]

I have Error 3075 (missing operator on [NumProspect]) in the following code
put on a form After Update Event :
If Not IsNull(Me!NomClient) Then
CurrentDb.Execute "UPDATE tblSecteurProspects SET " _
& "Statut = 'Client'," _
& "CodeClient = " & Chr$(34) & Me!CodeClient & Chr$(34) & " WHERE
NumProspect = " _
& Me!NumProspect, dbFailOnError
End If
Me!NumProspect (on Form) is a long Integer as well as NumProspect (on
tblSecteurProspects).
I don't get it since it's been working fine so far.
Can someone please help me??
Thanks in advance
Frankie
Access 2003

 

[Dennis]

Using chr(34) means that you are trying to put "CodeClient" into the record.
Not sure why you want it with quotes around. If this is what you want then
you are missing the single quotes around it (just like around Statut) to
signify string
& "CodeClient = '" & Chr$(34) & Me!CodeClient & Chr$(34) & "' WHERE

 

[Frankie via AccessMonster.com]

Thanks for your answer, Dennis.
CodeClient is a text field which is why I use Chr$(34).
I am a little confused with what you propose :
& "CodeClient = '" & Chr$(34) & Me!CodeClient & Chr$(34) & "' WHERE
Because It doesn't work, I still get the same message.
Any other ideas?

 

[Dennis]

Single quotes signify a string, so take out the chr$(34)'s leaving the single
quotes around your Me!CodeClient variable.

"CodeClient = '" & Me!CodeClient & "' WHERE

 

[Frankie via AccessMonster.com]

Nope, it doesn't work.

 

[Douglas J. Steele]

Try assign the SQL to a variable and writing the value of the variable to
the Immediate Window using Debug.Print.

See whether the SQL looks right to you. If it does, does it run?