Will someone please help with a definitive answer as to how to indicate that that a word in an Update query is a variable, and that it is the content of that variable that is to be written to the required field?
I have a variable, strThisUser (the current user's name) - that I want to store in a field called Created_By in a table called tblOrg_Name. The variable exists, and holds the required value as proved using Debug.Print strThisUser. When the user name is stored I also want to store the current date and time in the field Created_On.
I tried to do this using:
DoCmd.RunSQL "UPDATE tblOrg_Name " & _
"SET " & _
"tblOrg_Name.Created_On = Now(), " & _
"tblOrg_Name.Created_By = strThisUser;"
and got a syntax error in update statement.
I removed the line with Now(), and still got the error. I replaced the line with Now() and removed the line with strThisUser, and it worked OK, apart from not storing the user name of course. The error is in the way I'm using the variable, it seems
Reading through various posts on this forum, and others, produced a bewildering array of things, and combinations of things, in which to enclose the variable name. These include parentheses, quotes single and double, square brackets, ampersands, chr(xx), plus a few others which I don't remember right now. I have tried all of them, and nothing works.
As I understand that different versions of Windows, and different versions of Access, can cause things to behave other than expected and these may need to be known, Access 2000 running under Windows 2003 server.
First aid for the skull damage caused by banging my head against the wall over this one would also be appreciated Not angry - bleeding!!
I have a variable, strThisUser (the current user's name) - that I want to store in a field called Created_By in a table called tblOrg_Name. The variable exists, and holds the required value as proved using Debug.Print strThisUser. When the user name is stored I also want to store the current date and time in the field Created_On.
I tried to do this using:
DoCmd.RunSQL "UPDATE tblOrg_Name " & _
"SET " & _
"tblOrg_Name.Created_On = Now(), " & _
"tblOrg_Name.Created_By = strThisUser;"
and got a syntax error in update statement.
I removed the line with Now(), and still got the error. I replaced the line with Now() and removed the line with strThisUser, and it worked OK, apart from not storing the user name of course. The error is in the way I'm using the variable, it seems
Reading through various posts on this forum, and others, produced a bewildering array of things, and combinations of things, in which to enclose the variable name. These include parentheses, quotes single and double, square brackets, ampersands, chr(xx), plus a few others which I don't remember right now. I have tried all of them, and nothing works.
As I understand that different versions of Windows, and different versions of Access, can cause things to behave other than expected and these may need to be known, Access 2000 running under Windows 2003 server.
First aid for the skull damage caused by banging my head against the wall over this one would also be appreciated Not angry - bleeding!!
Last edited: