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?

Try testing your query in the query window to make sure it works. This link (http://allenbrowne.com/ser-61.html) will tell you why your query is not updateable.

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

>> 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! ;-)

[quote=LPurvis;820165
Where are txtSG and txtRAO coming from? Are they form controls?
Then they will need some form of qualification or substitution.
[/quote]

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

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)")

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

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)"")"