Help on "locked" query

[Frank]

Is there a way to bypass this?
I am using a select statement [ranking data] in a query column field to
compare two table fields with each other. The statement works 100% bit it
then locks the query in such a way that I cannot edit the query fields when I
use the query in a form to display the results. Without this statement the
query result can be edited, Any inputs into this query does not want to
change the existing data.
Could anybody please give a hint to what to do?
Thank you.

 

[John Spencer MVP]

Instead of using a subquery to establish the rank value, use the DCount function.

For instance, this query can be updated.
SELECT FAQ.fID, FAQ.fSubject, DCount("*","FAQ","fID<" & FAQ.[fid]) AS Rank
FROM FAQ

While this one cannot be updated
SELECT FAQ.fID, FAQ.fSubject
, (Select Count(*) FROM FAQ as F WHERE F.fID < FAQ.fID) AS Rank
FROM FAQ


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County