I had a form that used some Dlookups, but it was really slow, so I changed the form so that it is based on a query that uses subqueries in place of the Dlookups. But now I get an error that the recordset is not updateable. I googled this error and have tried to sift through the many explanations, but I can't seem to find a solution. I even changed the recordset type of the form and the underlying query to Dynaset (inconsistent updates) and it still doesn't work. Here's a skeleton view of my query:
Code:
SELECT tbl_Initiatives.pk_InitiativeID, tbl_Initiatives.Title, (SELECT DateOfEvent FROM tbl_EventLog WHERE fk_EventID=5 and fk_InitiativeID=tbl_Initiatives.pk_InitiativeID) AS ActualApprovalToDevelop, (SELECT DateOfEvent FROM tbl_EventLog WHERE fk_EventID=6 and fk_InitiativeID=tbl_Initiatives.pk_InitiativeID) AS ActualFOADevelopmentMtg, (SELECT DateOfEvent FROM tbl_EventLog WHERE fk_EventID=7 and fk_InitiativeID=tbl_Initiatives.pk_InitiativeID) AS ActualSubmitToENS, (SELECT DateOfEvent FROM tbl_EventLog WHERE fk_EventID=4 and fk_InitiativeID=tbl_Initiatives.pk_InitiativeID) AS ActualSubmitToDERA, (SELECT DateOfEvent FROM tbl_EventLog WHERE fk_EventID=3 and fk_InitiativeID=tbl_Initiatives.pk_InitiativeID) AS ActualSubmitToOD
FROM tbl_Initiatives INNER JOIN (tbl_Events INNER JOIN tbl_EventLog ON tbl_Events.pk_EventID = tbl_EventLog.fk_EventID) ON tbl_Initiatives.pk_InitiativeID = tbl_EventLog.fk_InitiativeID;
tbl_Initiatives has a 1:many relationship with tbl_Eventlog on pk_InitiativeID=fk_InitiativeID. tbl_EventLog has a 1:1 relationship with tbl_Events on fk_EventID=pk_EventID. tbl_Initiatives has a primary key on pk_InitiativeID, tbl_EventLog has one on pk_EventLogID, and tbl_Events has one on pk_EventID.
Any help would be most appreciated,
kman