Problem with an Update statement

[Cyd44]

I am trying to update a table but keep getting a compile error on the following code:-

Dim strSQL As String
strSQL = "Update [tblEmployees], Set [tblEmployees.strEmpPassword] = Me.newPsswd" _
         "WHERE strEmpPassword = Me.oldPsswd"
 
DoCmd.RunSQL strSQL

Have looked for syntax rules on web and feel that the above should work but Access Event will not accept it.

Can anyone help.

[NeoPa]

Cyd, I'm sure I must have posted links before on how to post here. The rules aren't that difficult, but you seem to be struggling with them. See When Posting (VBA or SQL) Code please before posting again. Had you followed the instruction at A2 we wouldn't need to be looking at this (or at least we would have had line number and error message to work with).

The problem is actually with your lines #2 & #3. This is a single line of code due to the continuation character, but the two strings are not added together as (presumably) intended because the ampersand (&) character is missing. It should read :

Dim strSQL As String
strSQL = "Update [tblEmployees], Set [tblEmployees.strEmpPassword] = Me.newPsswd" & _
         "WHERE strEmpPassword = Me.oldPsswd"
 
DoCmd.RunSQL strSQL

At this point you would have had a SQL failure on line #5 as the comma (,) after "[tblEmployees]" is not correct and there is no space between "Me.newPsswd" and "WHERE". "Me.newPsswrd" is also passed as a literal string within your SQL rather than passing its value and surrounding it in quotes to indicate it's a string value. There's also the matter of "[tblEmployees.strEmpPassword]" being an invalid field reference. It should probably end up something like :

Dim strSQL As String
 
strSQL = "UPDATE [tblEmployees] " & _
         "SET    [strEmpPassword] = '" & Me.newPsswd & "' " & _
         "WHERE  ([strEmpPassword] = '" & Me.oldPsswd & "')"
Call DoCmd.RunSQL(strSQL)

NB. Notice though, the separate parts of of the problem in the VBA (on one side) and the SQL (on the other).

[Cyd44]

NeoPa
Firstly I apologise for the not following rules. I had tried to follow them but made a typo on the [/code] comand and had {/code] instead. As a result it did not format the code. I did try homest but had finger trouble.

In relation to my question, once again you are a star. I had tried following web examples but they all seem to miss out the '" & statements. Many thanks

[NeoPa]

I'm very pleased to have helped. The [ CODE ] tags weren't a problem, but see my PM for more on that. You're coming along and I'd be surprised if you didn't go further. It's always hardest to get started.

[Cyd44]

Thats Very kind of you to say. Without your help I would be totally lost. I have a wierd problem now with the application itself and will post another question in order to keep to the rules