Solved Maintain Recordset Updatable With SubQry Single Result Over Many Rows?

dalski

Member
Local time
Today, 10:30
Joined
Jan 5, 2025
Messages
57
What technique can be used when returning a single value used as the entire field in a qry (Field 3; populated by a subquery) whilst also maintaining an editable recordset?

Code:
(SELECT TendersT.[Margin%] FROM TendersT WHERE (((TendersT.Current)=True)))

I have no relationship between the tables/ qry's nor do I want to. Surely it is possible?


1744101980669.png



EDIT - SQL:
Code:
SELECT ActvCompositionT.ActvCompoID, ActvCompositionT.ActvTitleID_FK, ActvCompositionT.ResID_FK, ActvCompositionT.Constant, ActvCompositionT.Output, ActvCompositionT.[Waste%], ActvCompositionT.RtNtDesired, ActvCompositionT.RtGrDesired, Switch(IsNull([RtNtDesired]) And IsNull([RtGrDesired]) And IsNull([Output]),"c",IsNull([RtNtDesired]) And IsNull([RtGrDesired]) And IsNull([Constant]),"o",IsNull([Output]) And IsNull([RtGrDesired]),"cN",IsNull([Constant]) And IsNull([RtGrDesired]),"oN",IsNull([Output]) And IsNull([RtNtDesired]),"cG",IsNull([Constant]) And IsNull([RtNtDesired]),"oG") AS MethodCode, Round(IIf(IsNull([Output]),[Constant]*[Waste%],1*[Waste%]),6) AS ResQtW, Round(IIf(IsNull([Output]),[Constant]*(1+[Waste%]),[Output]*(1-[ResQtW])),6) AS ResQtGr, CCur(IIf(IsNull([Output]),[ResQtGr]*[ResRt],[ResRt]/[ResQtGr])) AS RtNtExcDisc, (SELECT TendersT.[Margin%] FROM TendersT WHERE (((TendersT.Current)=True))) AS [Margin%], CCur([RtNtExcDisc]*[Margin%]) AS RtMarginGr, (SELECT TendersT.[DiscInline%] FROM TendersT WHERE (((TendersT.Current)=True))) AS [DiscInline%], ([RtNtExcDisc]+[RtMarginGr])*[DiscInline%] AS RtDisc, [RtNtExcDisc]*[Margin%] AS RtMarginNt, [RtNtExcDisc]+[RtMarginNt] AS RtSalePrice
FROM ResourcesT INNER JOIN ActvCompositionT ON ResourcesT.ResID = ActvCompositionT.ResID_FK;
 
Last edited:
use dLookup instead:
Code:
DLookup("[Margin%]", "TendersT", "Current=True")
 
use dLookup instead:

Thank you @arnelgp. Is dLookup evaluated only once? Then echoed on all the rows? If this is the case then I see it wont' be much of an issue however if the dLookup is used on each row instance then that will be considerably slow.
 
Thanks @cheekybuddha. I'm unable to post links with SPAM restrictions. Do a search for "Using a Subquery in a SELECT statement; red-gate "Simple Talk by Greg Larsen". I think there are instances where a basic subquery will yeild a single result... 'set as a column value' if I'm interpreting it correctly (no doubt not).
 
I'm not sure whether Access is as clever as SQLServer to determine whether the subquery is being used as a constant value rather than a correlated value which needs to be evaluated for every row.

If it is clever enough then it should also be able to identify that the DLookup() is also being used to return a constant value and should cache it for return in each row.

Only testing will reveal to you whether it has any material effect/disadvantage.
 
you should try it first on your actual data.
are you using Form, you can Hard-code the sql when the form load:
this will create (as far as on my test) an Editable recordset.
Code:
Private Sub Form_Load()
Const SRC AS STRING = _
"SELECT ActvCompositionT.ActvCompoID, ActvCompositionT.ActvTitleID_FK, ActvCompositionT.ResID_FK, ActvCompositionT.Constant, ActvCompositionT.Output, " & _
"ActvCompositionT.[Waste%], ActvCompositionT.RtNtDesired, ActvCompositionT.RtGrDesired, Switch(IsNull([RtNtDesired]) And IsNull([RtGrDesired]) And " & _
"IsNull([Output]),"c",IsNull([RtNtDesired]) And IsNull([RtGrDesired]) And IsNull([Constant]),"o",IsNull([Output]) And IsNull([RtGrDesired]),"cN",IsNull([Constant]) And " & _
"IsNull([RtGrDesired]),"oN",IsNull([Output]) And IsNull([RtNtDesired]),"cG",IsNull([Constant]) And IsNull([RtNtDesired]),"oG") AS MethodCode, " & _
"Round(IIf(IsNull([Output]),[Constant]*[Waste%],1*[Waste%]),6) AS ResQtW, Round(IIf(IsNull([Output]),[Constant]*(1+[Waste%]),[Output]*(1-[ResQtW])),6) AS ResQtGr, " & _
"CCur(IIf(IsNull([Output]),[ResQtGr]*[ResRt],[ResRt]/[ResQtGr])) AS RtNtExcDisc, XVALUE1 AS [Margin%], " & _
"CCur([RtNtExcDisc]*[Margin%]) AS RtMarginGr, XVALUE2 AS [DiscInline%], " & _
"([RtNtExcDisc]+[RtMarginGr])*[DiscInline%] AS RtDisc, [RtNtExcDisc]*[Margin%] AS RtMarginNt, [RtNtExcDisc]+[RtMarginNt] AS RtSalePrice " & _
"FROM ResourcesT INNER JOIN ActvCompositionT ON ResourcesT.ResID = ActvCompositionT.ResID_FK;"

Dim vlue as Double
dim rsrc As String

Vlue = DLookup("[Margin%]", "TendersT", "[Current]=True")
rsrc=Replace$(SRC, "XVALUE1", vlue)

vlue = DLookup("[DiscInline%]", "TendersT", "[Current]=True")
rsrc=Replace$(rsrc, "XVALUE2", vlue)

Me.Recordsource = rsrc

End Sub
 
Last edited:
If the query contains a DLookup, it will be slower than the same query doing the same thing using a sub-query. The reason is that the sub-query (a) is already built and therefore has a query plan as soon as the whole query statement gets started (b) doesn't have to open and close the implied recordset that underlies the Domain Aggregate function for each iteration of the records in the main query, because the initial analysis will open all required main and sub recordsets on initial analysis.

Faster still is the question of whether the value you want to look up is essentially a constant for the life of the query, in which case you could make the main query into a parameter query, then DLookup the value for the parameter and supply that value to the query when preparing to use it. If it is not a constant for the life of the query, this will not work, of course.
 
If the query contains a DLookup, it will be slower than the same query doing the same thing using a sub-query. The reason is that the sub-query (a) is already built and therefore has a query plan as soon as the whole query statement gets started (b) doesn't have to open and close the implied recordset that underlies the Domain Aggregate function for each iteration of the records in the main query, because the initial analysis will open all required main and sub recordsets on initial analysis.
This is too generalist a statement, Doc.

The subquery is in the selected field list, not part of the FROM clause. If it is a correlated subquery, (ie it's value depends on a value from each record) then it will be no faster than using DLookup in each row - in fact, the DLookup will be doing exactly the same as the subquery.

If it is independent of the row fields being selected then, yes, it may well be faster ...

... or not, if Access optimiser can work out that an equivalent DLookup is effectively a constant value and is able to cache the return value.
 
Thanks both for help. I changed the subquery to a dLookup fn using the query designer (which I would imagine it yeilds the same result as altering the SQL) & sadly the recordset remains uneditable. I know the issue is caused by

The qry is used in a form but also used in other uses so i need the qry itself. Thanks @arnelgp out of interest I pasted in your code in nr-8 (thank you for this) I got a syntax error on the src string.

Regardless I need it as a separate qry but just out of learning as much as I cannot see there being a difference/ benefit to coding the recordsource.

1744130397240.png
 
Are you sure your query is editable even without the DLookup/subquery/constant value?

Obviously all the fields based on functions (Switch()/Round()/CCur()) will not be editable, but check the vanilla fields - something else may be causing them to be uneditable.

Are ResourcesT.ResID and ActvCompositionT.ResID_FK both indexed?
 
OK, Dave... IF Access query optimizer is that smart, it MIGHT see the query as having an invariant part and MIGHT cache it. So I'll grant the odd chance that it can tell the difference. May I at least express a mild dose of skepticism?
 
Here is a useful link from Allen Browne, related to why a query cannot be used to perform updates.


Looking at the query, a couple of possible causes jump out right away. You have a sub-query, for example. You also need to assure that the fields involved in the JOIN are properly indexed. (We can't see that from here.) You also appear to have some VBA functions, but that can be an issue depending on environmental setup. AND you do appear to have some calculated fields. So there might be several issues to address at once.
 
I have no relationship between the tables/ qry's nor do I want to. Surely it is possible?
Seems a bit shortsighted to me. Why would you ever want to prevent the database engine from enforcing RI? The database engine is far better at this stuff than you are. When you do it, you need to remember to write code in EVERY SINGLE procedure that updates a table to ensure that your RI is being correctly enforced. How is that better than letting the database engine do its thing? Of course if you don't care about data integrity, then do it yourself.
Thank you @arnelgp. Is dLookup evaluated only once?
That depends on whether the domain function is correlated or not. If it uses criteria that takes a value from the current row, the domain function is executed for every single row in the recordset per force. A left join is a far better and more efficient solution for dLookup()s whenever you are using a code loop or a query since that gives the database engine better options to optimize the retrieval. If the criteria is hard coded and therefore unchangeable, the domain function is executed a single time and cached.

If a query is hardcoded as yours is, why not simply make it a querydef? Much easier to test and change later. At least by using a querydef, you save the time it takes to "compile" and calculate an execution plan for the SQL string every time you execute it. For querydefs, the execution plan is calculated and saved the first time the querydef is executed after a C&R.
 
Thank you all for your input, massively appreciated.

use dLookup instead:
Sorry @arnelgp, this solved it. I posted at a hectic time & forgot to convert the subqry on the margin% field to a dLookup.
The interesting question is WHY does a dLookup solve this. The subqry seems near identical in it's format. Why did I fall in this pitfall & have to rely on the helpful of this forum?

Are you sure your query is editable even without the DLookup/subquery/constant value?

Are ResourcesT.ResID and ActvCompositionT.ResID_FK both indexed?
Thanks @cheekybuddha, prior to posting if I remove the Margin% & Discount% columns the recordset was then editable.
ResourcesT.ResID was indexed but ActvCompositionT.ResID_FK was not indexed. I had no idea that joined fields required indexing & I don't think I spotted a warning on this either in the books "Access 2010 Inside Out" or "Access 2010 VBA Programming Inside Out" (awesome books & no doubt the warning is in there I just missed it most likely) thank you for the recommendation @Mike Krailo.

Here is a useful link from Allen Browne, related to why a query cannot be used to perform updates.

You have a sub-query, for example. You also need to assure that the fields involved in the JOIN are properly indexed. You also appear to have some VBA functions, but that can be an issue depending on environmental setup. AND you do appear to have some calculated fields.
Thanks @The_Doc_Man, he does some great stuff doesn't he. I read the article several times prior to posting & I couldn't spot anything relating to this problem in the article on this problem. I couldn't see Allen forewarn that any joined fields require indexing for example...

Seems a bit shortsighted to me. Why would you ever want to prevent the database engine from enforcing RI?
Thanks @Pat Hartman , it's just to temporarily show a value really for handy purposes. Found a problem & became obsessed as to why it was a problem & what was causing it.
That depends on whether the domain function is correlated or not. If it uses criteria that takes a value from the current row, the domain function is executed for every single row in the recordset per force.
Very interesting & thanks for explaining this; I'm not greatly intelligent so semantically differentiating between "domain functions" & parameters is a bit much for me atm. It seems "domain function" is a fancy way of explaining the range of the inputs of a function.
If a query is hardcoded as yours is, why not simply make it a querydef?
I only read a little on queryDefs & still am learning so much and unaware of best practices so thanks for bringing this to my attention. I took time off my job to build a tool to help me do my job & I am running out of time & money to get it done. I am massively grateful to all the help I have received on the forum. I have gone down a rabbit hole with Access & though I want to learn & implement the best ways possible my knowledge & experience is very limited.
 
Last edited:
It seems "domain function" is a fancy way of explaining the range of the inputs of a function.
Domain functions (or more usually Domain Aggregate functions) are Access' family of DLookup(), DCount(), DMax(), DAvg() ... etc.

ResourcesT.ResID was indexed but ActvCompositionT.ResID_FK was not indexed.
It's actually likely that ActvCompositionT.ResID_FK was indexed even though you hadn't done it explicitly - IIRC Access creates a hidden index for all foreign key fields.

I only asked because it was weird that the query would not be editable after I thought you had replaced the subquery with a DLookup().

As to why it is editable with the DLookup() and not with the subquery I'm not sure - one of Access' many quirks!

Glad you got it working. (y)
 
Why did I fall in this pitfall & have to rely on the helpful of this forum?
If any part of a query is not updateable, the whole is not updateable.
It seems "domain function" is a fancy way of explaining the range of the inputs of a function.
Domain function is the name of a class of functions that work on a set of records rather than on an individual field. I think they all start with "d" as in dLookup, dSum, dMax, etc

Think long and hard before you use domain functions (they are almost always correlated so the run once for each row in the query's recordset) inside a query or inside a code loop. If the data is being displayed on a form put the domain function into the ControlSource, this mitigates the slowness effect since Access only retrieves a few rows at a time so the form is displayed long before the entire recordset has been fetched - notice whether or not the y part of "x of y" is populated to verify this.
 

Users who are viewing this thread

Back
Top Bottom