What is the problem with this SQL syntax??? (1 Viewer)

[kamiri]

If Me.Active = False Then
If MsgBox("Are you sure you want to inactivate the selected user", vbYesNo) = vbYes Then
DoCmd.RunSQL "Update Country " & _
"INNER JOIN tblUser ON [Country].[Lead] = [tblUser].[UserID]" & _
"SET [Country].[Lead] = ," & _
"[Country].[BackUp] = ," & _
"[Country].[sLead] = ," & _
"[Country].[sBackUp] = ," & _
"[Country].[icUpdateRDO] = ," & _
"WHERE ([tblUser].[UserID] =)" & Me.UserID
Else
Me.Active = True
Exit Sub
End If
Else
Me.Active = True
End If
End Sub

 

[gemma-the-husky]

the obvious thing is that you aren't setting the fields to anything.

 

[kamiri]

I am setting the fields to nothing if the preceding "IF" statement evaluates to "FALSE". So if the field "ME.Active" (which is a check box) is false (unchecked) then this sql statement should set all the fields listed (lead, backup, sLead, sBackup) from table "Country" to nothing, where the UserID is equal to the "UserID" of the record displayed on the form. But I get an error stating there is a syntax error in the SQL statement. Any idea?

 

[Galaxiom]

"SET [Country].[Lead] = Null ," & _
 etc

 

[kamiri]

Tried it, hasn't worked

 

[robslob]

It looks like you have a trailing ',' in this line
"[Country].[icUpdateRDO] = ," & _

It should be
"[Country].[icUpdateRDO] = " & _

don't forget to include Galaxioms bit as well though

 

[stopher]

And also the brackets at the end will cause a problem

"WHERE [COLOR="red"]([/COLOR][tblUser].[UserID] =[COLOR="Red"])[/COLOR]" & Me.UserID

So the code should be something like:

If Me.Active = False Then
    If MsgBox("Are you sure you want to inactivate the selected user", vbYesNo) = vbYes Then
        DoCmd.RunSQL "Update Country " & _
        "INNER JOIN tblUser ON [Country].[Lead] = [tblUser].[UserID]" & _
        "SET [Country].[Lead] = Null," & _
        "[Country].[BackUp] = Null," & _
        "[Country].[sLead] = Null," & _
        "[Country].[sBackUp] = Null," & _
        "[Country].[icUpdateRDO] = Null " & _
        "WHERE [tblUser].[UserID] =" & Me.UserID
    Else
        Me.Active = True
        Exit Sub
    End If
Else
    Me.Active = True
End If

I don't know why you've chosen to do the update with an inner join. You could just do your where clause like this and eliminate the join with tblUser altogether.

"WHERE [Country].[Lead] =" & Me.UserID

The Exit Sub in the middle of the code is not required. The sub is going to exit anyway without doing anything else.

Your last Else clause is intriguing. The else clause is where Me.Active is not false. So is it not already true and therefore this else clause is redundant?