Access Select Query recordset not Updateable

Question

I have a Access 2010 Form that is linked to Sql Server and I am using a select query that works correctly until I add an Inner Join and then the field becomes unable to update (Recordset is not Updateable) This is the query I am using but cant check the field Yes(this is a checkbox), if I remove the inner join it works. column nm has can have more than one name in it so to get the latest name I am using the inner join with the max to give me the latest name otherwise it will show both names in the nm column.

SELECT tblA.Reference
 TblA.Yes,
 TblA.Nm,
 TblA.PKID
FROM TblA 

INNER JOIN (
SELECT 
Reference,
max(PKID) AS TRANID 
FROM TblA 
GROUP BY Reference)  AS TblB ON TblA.Reference = TblB.Reference AND 
TblA.PKID = TblB.TRANID)

WHERE TblA.Reference Like "*" & [forms]![NewPayments]![Combo1] & "*" AND 
TblA.Nm Like "*" & [forms]![NewPayments]![ClientName] & "*" AND 
TblA.Account Like "*" & [forms]![NewPayments]![ACN] & "*" AND 
TblA.Query Is Null ;

I have read a few things on this but after hours of trying to get my result I am still unable, does this mean what I am trying to do is not possible? Thanks — New2Programming
– New2Programming, Commented May 17, 2018 at 11:18
Well... you haven't actually told us what you want to do. We can't magically make this query updateable, no. But you aren't selecting anything from TblB, so I don't know why you have it there. — Erik A
– Erik A, Commented May 17, 2018 at 11:21
Sorry for not explaining, column nm has can have more than one name in it so to get the latest name I am using the inner join with the max to give me the latest name otherwise it will show both names in the nm column. — New2Programming
– New2Programming, Commented May 17, 2018 at 12:08
Consider editing the question to include that. That would make it not be a duplicate. — Erik A
– Erik A, Commented May 17, 2018 at 12:29

Erik A · Accepted Answer · 2018-05-17 12:28:29Z

1

If you want to use aggregates only to select specific entries, you can use EXISTS instead of INNER JOIN to keep the query updateable.

SELECT tblA.Reference
 TblA.Yes,
 TblA.Nm,
 TblA.PKID
FROM TblA 
WHERE EXISTS (
    SELECT 1
    FROM TblA As TblB
    WHERE TblA.Reference = TblB.Reference
    HAVING TblA.PKID = max(TblB.PKID)
) AND TblA.Reference Like "*" & [forms]![NewPayments]![Combo1] & "*" AND 
TblA.Nm Like "*" & [forms]![NewPayments]![ClientName] & "*" AND 
TblA.Account Like "*" & [forms]![NewPayments]![ACN] & "*" AND 
TblA.Query Is Null ;

answered May 17, 2018 at 12:28

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

New2Programming Over a year ago

Thank you so much for this! and sorry again for not being clear with my question!

Collectives™ on Stack Overflow

Access Select Query recordset not Updateable

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related