By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,653 Members | 1,843 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,653 IT Pros & Developers. It's quick & easy.

Can't update a RecordSet

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. Me.RecordSource = "SELECT Projects.* FROM _
  2. (Projects INNER JOIN LastStatusDates ON _
  3. Projects.ProjectID = LastStatusDates.ProjectID)INNER JOIN StatusReports _ 
  4. ON (Projects.ProjectID = StatusReports.ProjectID) AND _
  5. (LastStatusDates.Last_Status_Date = StatusReports.StatusDate) _
  6. WHERE (((StatusReports.Status)='active')) _
  7. 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 "StatusReports" which contains all of the Status' for a given project. I want to reference the last status, hence the Query "LastStatusDates".

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.OrderByOn = True" ahead of this code, but no luck...

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

Thanks in advance
Apr 29 '09 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 2.5K+
P: 2,545
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.

Apr 30 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.