VBA function with nested UPDATE SQL (1 Viewer)

path479

Registered User.
Local time
Tomorrow, 06:55
Joined
Jan 4, 2016
Messages
22
When I ran the following function I got the error message “Operation must use an updateable query.”:

Public Function UpdProjNLA()
NLA_SQL = _
"UPDATE Project " & _
"SET Project.projNLA_area = " & _
"(SELECT Sum(Floor.formalVSml_no * Floor.nla) " & _
"FROM Floor WHERE Floor.proj_id = txtproj_id) " & _
"WHERE project.proj_id = txtproj_id;"
DoCmd.RunSQL NLA_SQL
End Function

I got the same error message when I tried to run the SQL query in SQLView.
If I tried to run this query as 2 separate queries (i.e. the inner select query, then an update query) , they work fine.
Background information:
1) the above function is attached to a Save button on a form
2) txtproj_id is a text box from the form.
3) The Project table and Floor table are joined by the proj_id field.

Would someone be able to assist on where the issue is and how to fix it?


 
Code:
Public Function UpdProjNLA()
dim dbl as double
dbl = nz(dsum("[formalvsml_no]*[nla]", "floor", "[proj_id] = " & [COLOR=Blue][txtproj_id][/COLOR]), 0)
NLA_SQL = _
"UPDATE Project " & _
"SET Project.projNLA_area = " & dbl & " " & _ 
"WHERE project.proj_id = " & [COLOR=Blue][txtproj_id][/COLOR] & ";"
DoCmd.RunSQL NLA_SQL
End Function

try qualifying the blue-colored text with the proper form name, ie:

Forms!yourFormname![txtproj_id]
 
If you are doing a calculation on child records and then saving that result in the parent, I would avoid that. Consider SQL like this . . .
Code:
SELECT p.txtproj_id,
    (
    SELECT Sum(f.formalVSml_no * f.nla) " & _
    FROM Floor As f
    WHERE f.proj_id = p.txtproj_id
    ) As projNLA
FROM Project As p
. . . which calculates that result on the fly as required. In this case, remove the projNLA field from the project table, and always calculate it, never store it. Some facts are, by definition, calculations. In that case, don't store those facts, always calculate them from the raw data.

Hope this helps,
 
Thank you arnelgp and MarkK. Both your suggestions works great.

Arnelgp. Thank you for suggesting on using DSum. I had tried it before but didn't work. I now realised I got the proj_id field wrong.

MarkK, you are right. I should avoid updating the parent with calculation on child records. Thank your for the advice.
 

Users who are viewing this thread

Back
Top Bottom