Another CurrentDB.Execute question. Error 3705

[sheusz]

Thank you for viewing my question.

I have been looking through this forum and others for a solution, but haven't been able to find one that works in this way.

I want to update xEmployees.SessionCount with the value 0, when a certain condition is met, but am having issues with the Where clause (I think).

Basically SessionCount will be incremented by 1 until a threshold is reached, at which point I want to reset the value of SessionCount to 0.


The code below throws up Error 3705.

CurrentDb.Execute "UPDATE xEmployees SET SessionCount = 0" & "WHERE xEmployees.WebUsername = " & LoggedUserName

LoggedUserName is a public variable.

I've successfully done similar things in the past, but always based the Where clause on values taken from a form, which seems to be what most examples I have found show.

I'm pretty sure it's a syntax issue, but haven't been able to figure it out.

Any assistance will be greatly appreciated.

[Welshgasman]

And 3075 is?

Looking at your code, you have no spaces between 0 and where?
Do yourself a favour. Put the sql into a string variable and debug.print it until you get it correct. Then use that in the Execute command.

Why are you even concatenating that when it could be a hardcoded string?
Also if LoggedUserName is a string then you need to surround it with single quotes or triple double quotes if that would contain a single quote.

[sheusz]

Hi Welshgasman

Thank you for your reply and suggestions. I'm running the code in the immediate window to test it out.

I've changed the code to

CurrentDb.Execute "UPDATE xEmployees SET SessionCount = 0 " & "WHERE xEmployees.WebUsername = " & "LoggedUserName"

But now get the error 3061. Too few paramaters. Expected 1


I'm interested in your comment, but don't quite follow.

Why are you even concatenating that when it could be a hardcoded string?

[jojowhite]

you may also try:

CurrentDb.Execute "UPDATE xEmployees SET SessionCount = 0 WHERE xEmployees.WebUsername = '" & Replace$(LoggedUserName,"'", "''") & "'"

[sheusz]

Hi JoJo

Thank you for the reply. Your suggestion works, but I don'r understand why, and I'd like to.

I'm good up to this point. Replace$(LoggedUserName,"'", "''") & "'"

It's not something I've seen before. Could you explain a little please?

you may also try:

CurrentDb.Execute "UPDATE xEmployees SET SessionCount = 0 WHERE xEmployees.WebUsername = '" & Replace$(LoggedUserName,"'", "''") & "'"

[jojowhite]

if your LoggedUserName variable has single quoute name like:

O'Donnel

your query will not work and you will get error message.

the Expression is simply means to replace all occurrence of single quote (')
with double single quote (''). After replacement your query will work.

don't worry if youre LoggedUserName don't have single quote, but
who knows in the future.

[sheusz]

Thanks Jojo

That's a good point. I never thought of that. Thank you.

[CJ_London]

LoggedUserName is a public variable.

shouldn’t this be a tempvar to use in a query? Only way a public variable can be used is via a function. Always assumed that needed to be a function that returned the value but perhaps using the replace function is also a way

[Welshgasman]

Hi Welshgasman

Thank you for your reply and suggestions. I'm running the code in the immediate window to test it out.

I've changed the code to

CurrentDb.Execute "UPDATE xEmployees SET SessionCount = 0 " & "WHERE xEmployees.WebUsername = " & "LoggedUserName"

But now get the error 3061. Too few paramaters. Expected 1


I'm interested in your comment, but don't quite follow.

This

SET SessionCount = 0" & "WHERE

when it can just be

SET SessionCount = 0 WHERE