CODEWITHSUNDEEP
ms-access
New2Programming
New2Programming

Reputation: 383

Access Select Query recordset not Updateable

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 ;

Upvotes: 0

Views: 140

Answers (1)

Erik A
Erik A

Reputation: 32642

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 ;

Upvotes: 1

Related Questions