Sorting records makes recordset non-updateable

[Carl Colijn]

Hi all!

TL;DR: I've got a bound combobox on my continuous form. The combobox is bound to a field from the form's record source (an ID field), but it shows data from a helper lookup query (based on a second table), that translates the bound ID field to a description value. It's thus showing other data than the bound field, and that other data is not in the form's record source either. When the user sorts the form in Access 2010 on this combobox field, the records become non-updateable. Removing the sort makes the records updateable again. This problem didn't occur in Access 2003.



I've already found the source of this problem: Access 2010 has become stricter in when a recordset is still updatable. Since the data shown by the combobox isn't in the form's record source, Access probably must makes a temporary join on the form's record source with the lookup query used by the combobox in order to sort the form on that looked-up data. Since the data returned by the lookup query doesn't contain a primary key from the underlying table (the table doesn't even have one ), Access 2010 decides it cannot safely identify the record being edited through this temporary join, and makes the resulting joined record set non-updateable. Access 2003 (and presumably 2007 from what I've heard) aren't that fussy and do allow updates.

The obvious solution is to set a primary key on the ID field in the lookup table. This does work wonders, but it is not possible with my database, since the ID field cannot satisfy the uniqueness a primary key desires. In the lookup table there is also another ID (historical period ID), which together with the ID used could be used as a composite primary key, but this in turn will not work either because the combobox only uses the ID field and not the period ID field. Adding the period ID to the lookup query and even the combobox's displayed columns doesn't make Access 2010 happy enough either.



It's an interesting puzzle... The Access application is split into multiple central backend databases containing only tables and all data (per period ID), and locally installed frontend databases containing all queries, forms, VBA and the linked tables. Making changes to the frontend would necessitate all clients to update (lesser evil), and changing the table definitions would mean a complete data overhaul in all backend databases AND updating all clients (greater evil). I can set/clear indices and/or primary keys on the backend tables (not evil at all!), but I fear I cannot fix this problem with that alone... Oh, and it must remain in Access 2003 format since I've also got 2003 clients I know I can fix this, but it will involve some of the greater evils in the aforementioned list. Can anyone come up with the simplest / least invasive answer?

I've attached a sample database showing this problem; I've described it's design below. It's a tremendously oversimplified version, but it does demonstrate the problem described. None of the tables have primary keys defined. The field PeriodID allows me to keep old records in the database, but to also be able to show only records from a certain period (or the current period which is defined as PeriodID = 0). As said, I can assign a (composite) primary key in table T_Items on both fields PeriodID and ID, but not the field ID alone. The problem arises when the user opens the form F_Comments, puts the cursor on control 2 (the combobox using query Q_AllItems_ByName as row source), and then sorts the records - after that, the form becomes read-only.

Table T_Items - all defined items

• field ID: text - the item's ID
• field Name: text - the item's name
• field PeriodID: number - to which period this entry belongs

Table T_Comments - all defined comments

• field ID: text - the item this comment is for (many to one)
• field Comment: text - the comment
• field PeriodID: number - to which period this entry belongs

Table T_Settings - the database settings

• field CurrentPeriodID: number - the selected period to work with

Query Q_AllComments - all comments to show using the current settings

    SELECT *
    FROM T_Comments
    WHERE PeriodID IN (SELECT CurrentPeriodID FROM T_Settings);

Query Q_AllItems_ByID - all items sorted by their ID

    SELECT ID, Name
    FROM T_Items
    WHERE PeriodID IN (SELECT CurrentPeriodID FROM T_Settings)
    ORDER BY ID;

Query Q_AllItems_ByName - all items sorted by their name, used for name lookup on form

    SELECT Name, ID
    FROM T_Items
    WHERE PeriodID IN (SELECT CurrentPeriodID FROM T_Settings)
    ORDER BY Name;

Form F_Settings - changes database settings

• bound to T_Settings
• control 1: editbox bound to CurrentPeriodID

Continuous form F_Comments - all comments

• bound to Q_AllComments
• control 1: combobox bound to field ID, using Q_AllItems_ByID as row source
• control 2: combobox bound to field ID, using Q_AllItems_ByName as row source, bound to column 2 showing only column 1
• control 3: editbox bound to field Comment

Thanks in advance!
Carl Colijn

 

[spikepl]

A lot of bla -very commendable that you describe your tables/queries. But I cannot easily find the steps required to reproduce your problem - perhaps they are buried in the text somewhere?

 

[Carl Colijn]

A lot of bla -very commendable that you describe your tables/queries. But I cannot easily find the steps required to reproduce your problem - perhaps they are buried in the text somewhere?

I've attached a sample database with the exact same code, but I thought adding all code in one place (it's not much) would clarify things a bit. I also already found the reason for this problem, so I added that as well so people wouldn't need to investigate first.

Anyway, once you're in the attached database, open the form F_Comments, and all records will be updatable. You can sort by all fields on the form EXCEPT the Name field, and all records remain updatable. Once you sort on the Name field though, you're out of luck and the form becomes read-only...

I know there are many solutions, but all of the ones I can think of require a rather substantial rewrite of the frontend and/or backend. Since the real database is a complicated system with lots of (structurally identical) backend databases and since upgrading all users' frontends is a hassle I hope anyone can point out a solution requiring only minimal changes...

Kind regards,
Carl Colijn

 

[spikepl]

I tried your recipe but the data remains updateable after sorting. I have Access 2007 on WinXP. Was the error supposed to appear in the db when run in <A2010 or not?
btw: You know that Name is a reserved word?

 

[Carl Colijn]

I tried your recipe but the data remains updateable after sorting. I have Access 2007 on WinXP. Was the error supposed to appear in the db when run in <A2010 or not?
btw: You know that Name is a reserved word?

The "error" doesn't appear in 2003 nor in 2007, but Access 2010 got more strict in when a recordset is still updatable, and that change of rules broke this functionality.

Name being a reserved word didn't hinder in 2003/2007/2010 though, so that's a red herring here.

 

[CJ_London]

I have 2010 and just tried as suggested and get the same problem - the solution is to make your form recordset type dynaset - inconsistant updates.

Also I note the file is an mdb - have you tried updating to accde? This may also have an effect

 

[Carl Colijn]

I have 2010 and just tried as suggested and get the same problem - the solution is to make your form recordset type dynaset - inconsistant updates.

An enormous thank you! That was indeed the simple fix I hoped someone could point me to. I hoped I only needed to change the backend databases though (like setting primary keys etc.), but this is the next best thing.

Although "dynaset - inconsistant updates" always makes me afraid it'll mess something up big time, it cannot hurt in this scenario since the form's record source is only a complicated WHERE'd on the actual source table join on some auxiliary tables with fields the user can't modify anyway.

Also I note the file is an mdb - have you tried updating to accde? This may also have an effect

Can't do that; I've still got 2003 users using it. Besides: the actual database is way bigger than the small sample DB I attached, switching to accde would mean a lot of work.