Invalid SQL Statement in Update Query

[Danick]

Would someone please let me know what I'm doing wrong in this code?
I'm trying to do an update query and suppress the warnings that show up when you run the code. The code works fine on it's own without the SQL string. Thanks for your support.

Dim strSQL As String
strSQL = "UPDATE tblCountry INNER JOIN Query1 ON tblCountry.CountryID=Query1.CountryID SET tblCountry.ChkDST = Query1.Active;"
CurrentDb.Execute strSQL, dbFailOnErrors

 

[Ranman256]

put the sql in a query. It will tell you what is wrong.
use queries, they dont get the syntax wrong. (avoid sql)

 

[isladogs]

put the sql in a query. It will tell you what is wrong.
use queries, they dont get the syntax wrong. (avoid sql)

Sorry but I totally disagree with the statement 'avoid sql'
In any case I believe the OP took this SQL from a query

As you are setting a field equal to the value of a control, you need to enclose in quotes. It appears to be a boolean (yes/no field) so the following should work

UPDATE tblCountry INNER JOIN Query1 ON tblCountry.CountryID=Query1.CountryID SET tblCountry.ChkDST = " & Query1.Active & ";

To assist with conversion from SQL to VBA or vice versa, you many find my SQL to VBA and back again utility useful
https://www.access-programmers.co.uk/forums/showthread.php?t=293372

The latest version is in post #5

 

[jdraw]

Danick,
Further, when building sql within vba, ALWAYS use DEBUG.Print to see how Access renders your string. You may find some "obvious" errors in syntax.
You can use that string in the Query window to see if it executes.

 

[Danick]

Sorry but I totally disagree with the statement 'avoid sql' In any case I believe the OP took this SQL from a query

As you are setting a field equal to the value of a control, you need to enclose in quotes. It appears to be a boolean (yes/no field) so the following should work

UPDATE tblCountry INNER JOIN Query1 ON tblCountry.CountryID=Query1.CountryID SET tblCountry.ChkDST = " & Query1.Active & ";

Thanks Colin, but the code is still not working.
As you correctly observed, the code did come originally from an update Query. Here is the original code which does work.

UPDATE tblCountry INNER JOIN Query1 ON tblCountry.CountryID=Query1.CountryID SET tblCountry.ChkDST = Query1.Active;

The only thing I am trying to do in improving this is to add some VBA to get rid of those annoying popup messages - one that says that I'm about to update some records and then another one that tells me how many records I've updated.
So I thought I could just wrap a couple of lines of code around the basic query and suppress those popups. But I'm having a hard time getting that to work.

 

[Minty]

Unless it's a typo this shouldn't have as S on the end

CurrentDb.Execute strSQL, dbFailOnError[COLOR="Red"]s[/COLOR]

 

[isladogs]

Well spotted minty. I missed that

 

[Danick]

Thanks Minty for finding that error, but fixing the TYPO didn't help either.
I did however manage to get it to work.
To get it working, I left the working update query as is and then created a button on the form to run the update query.

Private Sub cmdUpdateDST_Click()
On Error GoTo Err_cmdUpdateDST_Click

If MsgBox("Are you sure you want to Update Daylight Savings time?", vbQuestion + vbYesNo, "Update DST?") = vbYes Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateDST"
DoCmd.SetWarnings True
DoCmd.Requery
Else
Exit Sub
End If

Exit_cmdUpdateDST_Click:
    Exit Sub

Err_cmdUpdateDST_Click:
    MsgBox Err.Description
    Resume Exit_cmdUpdateDST_Click
    
End Sub

I'm sure there is a way of suppressing the warning without having to create a button, but at least I've got it working now.

Thanks everyone for your help.

 

[isladogs]

Out of interest, what did happen when you ran the code supplied & with minty's correction? Error? No records updated? Access didn't respond?

 

[Danick]

Out of interest, what did happen when you ran the code supplied & with minty's correction? Error? No records updated? Access didn't respond?

The error was the same as the title of this post. I'm guessing that Access doesn't like to see any quotes in the update query. At least I haven't been able to get them to work unless I build the string outside like I did in this case. Could be wrong...

 

[isladogs]

The error was the same as the title of this post. I'm guessing that Access doesn't like to see any quotes in the update query. At least I haven't been able to get them to work unless I build the string outside like I did in this case. Could be wrong...

No - that's not the issue
Here's the first one of mine I came across & I have many others far more complex than this:

"UPDATE tblMessageSettings SET tblMessageSettings.Value = '" & strValue & "' WHERE tblMessageSettings.Alias = '" & strAlias & "'"

Do have a look at the SQL to VBA converter utility I mentioned in an earlier post - link also provided

 

[JHB]

It should run without problem.
Are you doing any calculation in "Query1"?
Only for finding the problem, change "Query1" to a "Make Table" query, then link to that table in your SQL string, to see if that runs without problem.