Little help with SQL in VBA

[foxy]

Hi,

I am trying to write to an ID number to a field using the following SQL:

DoCmd.RunSQL "UPDATE tblUser 
                SET sg_id = (SELECT SG_ID 
                             FROM tblSource_Group 
                             WHERE SG_name = txtSG 
                             AND RAO_ID = (SELECT RAO_ID 
                                           FROM tblRAO 
                                           WHERE RAO_name = txtRAO
                                           )
                             )"

I get an error which says:

"Operation must use an updateable query"

Any ideas how I can get this to work? It seems as if I cannot use subselects in RunSQL?

 

[Rabbie]

Try testing your query in the query window to make sure it works. This link will tell you why your query is not updateable.

 

[foxy]

I have tried the SQL just in a query window:

UPDATE tblUser SET sg_id = (SELECT SG_ID 
                             FROM tblSource_Group 
                             WHERE SG_name = [Forms].[frmAddNewPerson].[txtSG] 
                             AND RAO_ID = (SELECT RAO_ID 
                                           FROM tblRAO 
                                           WHERE RAO_name = [Forms].[frmAddNewPerson].[txtRAO]
                                           )
                             );

I still get the same error.

The link you posted states that a reason a query is read-only is that it has subselects. But subselects are a perfectly valid SQL statement. Can RunSQL not handle subselects?

How can I get around this without using subselects?

Cheers

 

[LPurvis]

>> It seems as if I cannot use subselects in RunSQL?

Actually - it's in an Update query that you cannot use them (inline subqueries).
They're not updatable (as they return a fixed scalar value) and so not eveyr part of the statement is and hence the entire query isn't updatable. (That's Jet's rationale - other engines, such as SQL Server, see things differently thankfully).
Of course - it can be argued that almost any update statement like this is refering to other data and hence the act of executing the update is in some way violating normalisation. ;-)

Where are txtSG and txtRAO coming from? Are they form controls?
Then they will need some form of qualification or substitution.

DoCmd.RunSQL "UPDATE tblUser " & _
"SET sg_id = Dlookup("SG_ID", "tblSource_Group", "SG_name = txtSG " & _
"AND RAO_ID = (SELECT RAO_ID FROM tblRAO " & _
"WHERE RAO_name = txtRAO)"

Of course the DAO method .Execute is generally preferable to RunSQL. (Though you do maintain access to Access expression with the latter for convenience).
You could create a query to lookup the value from too - instead of the slightly convoluted Where clause - Joins are preferable for performance - particularly in Jet.

e.g.
CurrentDb.Execute "UPDATE tblUser " & _
"SET sg_id = Dlookup("SG_ID", "qrySourceRAO", "SG_name = txtSG " & _
"AND RAO_name = txtRAO)"

Cheers.

Allen Browne's list on updatable queries is pretty definitive though so definitely still read it. (And anything else there too! ;-)

 

[foxy]

Yes they are form controls which have a rowsource of the values in the tables.

I have used the method you described above:

DoCmd.RunSQL "UPDATE tblUser "& _"SET sg_id = Dlookup("SG_ID", "tblSource_Group", "SG_name = [Forms].[frmAddNewPerson].txtSG "& _"AND RAO_ID = (SELECT RAO_ID FROM tblRAO "& _"WHERE RAO_name = [Forms].[frmAddNewPerson].txtRAO))"

But the line of VBA goes red and says that the syntax is incorrect.

Can you see where I am going wrong?

Cheers

 

[LPurvis]

OK if you're doing it in one line....

DoCmd.RunSQL "UPDATE tblUser SET sg_id = Dlookup("SG_ID", "tblSource_Group", "SG_name = [Forms]![frmAddNewPerson]!txtSG AND RAO_ID = (SELECT RAO_ID FROM tblRAO WHERE RAO_name = [Forms]![frmAddNewPerson]!txtRAO)")

 

[foxy]

Im still getting a syntax error for some reason. Sorry to be a pain in the arse but its really frustrating me!

 

[LPurvis]

It is easier on multiple-lines to see what you're doing, trust me. ;-)

DoCmd.RunSQL "UPDATE tblUser SET sg_id = Dlookup(""SG_ID"", ""tblSource_Group"", ""SG_name = [Forms]![frmAddNewPerson]!txtSG AND RAO_ID = (SELECT RAO_ID FROM tblRAO WHERE RAO_name = [Forms]![frmAddNewPerson]!txtRAO)"")"