Recordset is not Updateable - Group By

[gray]

Hi All

Windows XPPro
Access 2002/2007

I have a form whose recordsource I build dynamically using VBA. Various controls are bound using controlsources.

The form's table has 3 'Identity' fields:

1) Unique_No - Autonumber - Indexed - No duplicates
2) Rec_ID - Long Integer - Indexed - Duplicates OK
3) Version - Long Integer - Not Indexed.

Use of these fields permits several generations of any particular record, that is to say that when a record is edited a new version can be created. A 'family' of versions is identified by the Rec_ID field... e.g.

Unique_No 12787
Rec_ID - 12787
Version - 1

Unique_No 12834
Rec_ID - 12787
Version - 2

Unique_No 12901
Rec_ID - 12787
Version - 3

These are effectively the same 'real world' record which has three generations. Any given record can be accessed using it's unique no.

In order to display the latest generations [only] of records from any given table, I set the form recordsource as below:

SELECT Tbl.* FROM (( Tracks As Tbl ) INNER JOIN (SELECT max(Version) as Max_Version,ID FROM Tracks GROUP BY ID) AS Max_Results ON (Tbl.Version=Max_Results.Max_Version) AND (Tbl.ID=Max_Results.ID)) WHERE (Tbl.Deleted=True or Tbl.Deleted=False) ORDER BY Tbl.Name

I was kindly helped with this, rather cunning, query by one of the forum members and it works brilliantly!

However, when I try to edit any detail in my form, Access informs me that "this recordset is not updateable".

From my research, I think use of the GROUP BY is causing this.

Can anyone suggest a work around or possibly another method of extracting the highest versions of records?

Thanks in anticipation.

 

[MarkK]

It's not so much the Group By clause as it is this sub-query ...

(SELECT max(Version) as Max_Version,ID FROM Tracks GROUP BY ID)

that causes the query to not be updatable.
But this whole thing looks a bit sloppy to me. Like the WHERE clause selects records where deleted is true or deleted is not true which is ALL the records, so it does nothing.
If you must have the version number you can calculate it independently for each record using something like ...

DCount("*", "Tracks", "RecID = 12787 AND UniqueID <= 12901")

If you use that to calculate the version number when you need it, you can simplify your SQL to ...

SELECT * FROM Tracks ORDER BY [Name]

which will allow updates.
Cheers,

 

[gray]

Hiya

Ah.. so it might be the sub-query itself rather than just the GROUP BY within it... As a rule then, do sub-queries always make recordsets non-updatable? I hope not, I intended to use sub-queries on most of my forms...

thanks

p.s. I allow administrators to see records that have been marked as deleted so I leave the bracketed delete=true/false in the recordsource as anchor used by a 'replace' string call in my code.

 

[vbaInet]

Here's a good piece of info for you:

http://allenbrowne.com/ser-61.html