Update Query With SubQuery

[WickidWe]

I am just trying to get the hang of writing SQL statments



below is an SQL Statement I am trying to work on in VBA but no luck

Dim dbs As Database
Dim varItm As Variant
Set dbs = CurrentDb
Dim sSQL As String

sSQL = "Update tblEmpDetails " & _
"Set InVisible = True " & _
"WHERE (SELECT ed.IsVisible From tblEmpDetails ed " & _
"JOIN tblPickExemptions pe " & _
"ON ed.PermFctn = pe.PermFctn " & _
"WHERE ed.PermFctn = pe.PermFctn); "

dbs.Execute ("sSQL ")

It doesnt have to be in a string but I am trying different ways just to get the hang of it

[June7]

Use Access query builder to help get the correct sql. Then copy/paste from SQL View to VBA.

Try:

"UPDATE tblEmpDetails " & _
"JOIN tblPickExemptions " & _
"ON tblEmpDetails.PermFctn = tblPickExemptions.PermFctn " & _
"SET InVisible = True " & _
"WHERE tblEmpDetails.PermFctn = tblPickExemptions.PermFctn

[WickidWe]

your code as follows

Dim dbs As Database
Dim varItm As Variant
Set dbs = CurrentDb
Dim sSQL As String
sSQL = "Update tblEmpDetails " & _
"JOIN tblPickExemptions " & _
"ON tblEmpDetails.PermFctn = tblPickExemptions.PermFctn " & _
"Set InVisible = True " & _
"WHERE tblEmpDetails.IsVisible " & _
dbs.Execute ("sSQL ")

This is the error msg

[June7]

Do not enclose variables in " marks.

dbs.Execute sSQL

[WickidWe]

I am Now getting this error with my SQL

[June7]

Ooops. Probably need INNER JOIN.

Why don't you build and test the sql with an Access query object? Use a copy of the tables until you are sure of the results.

[WickidWe]

I am not sure how to make an Update Query with the query builder

because there is no "FROM"

[June7]

Open query builder, select tables, join them, click UPDATE from ribbon Design tab, drag fields to the grid.

Or copy/paste the suggested sql into query builder SQL View. Edit it there to remove all the VBA stuff (add the INNER keyword) then switch to Design View to see the structure there.