Can't update a RecordSet

[RogerThis]

I have basically the same problem... (and I'm a Newbie). I have a sub-form that is being filtered based on the combo boxes available. The underlying query is straight forward for most selections... meaning the fileds are contained in a single table; no issues there. In one combination, the key field, status, is in a "related" table and a join is required. When that happens, the Project data is not editable.

MyCode when "only Active" is selected:

Me.RecordSource = "SELECT Projects.* FROM _
(Projects INNER JOIN LastStatusDates ON _
Projects.ProjectID = LastStatusDates.ProjectID)INNER JOIN StatusReports _ 
ON (Projects.ProjectID = StatusReports.ProjectID) AND _
(LastStatusDates.Last_Status_Date = StatusReports.StatusDate) _
WHERE (((StatusReports.Status)='active')) _
ORDER BY Projects.ProjectID"

It gets the right data, but I can't update the data in the Table "Projects" but I can update the data in the Table "StatusRepo rts" which contains all of the Status' for a given project. I want to reference the last status, hence the Query "LastStatusDate s".

The later query contains a "Max" function on the date.

This code, written by someone else, did work, but apparently I've messed it up. I tried adding "Me.OrderBy On = True" ahead of this code, but no luck...

I hope I was clear. Any help would be awesome.

Thanks in advance

[Stewart Ross]

Hi. I have moved your post from the thread you attached it to, as the question you are now posing is quite different to the one that was previously answered.

There are many reasons why Access queries become non-updatable - so many that we have an Insight article with a link to definitive data at MSDN. The article is linked here for your interest.

Short answer in your case is that when you join your updatable table to a query that uses an aggregate function (Max in your case) the query will not be updatable. Aggregate functions such as Count, Max, Min and so on summarise data from many other rows of the table concerned, and it appears that the join to a summary row which is at a different aggregation level than the original table prevents updatability where such a function is used.

An alternative if the Max function is required is to remove the aggregate query in the base query for your updatable dataset, and substitute a calculated field that uses a domain aggregate function to lookup the maximum concerned. DMax is the domain aggregate equivalent of Max, and its use allows the lookup of a value without preventing updatability. The downside is that DMax can be slow in operation when used repeatedly.

-Stewart