recordset is not updateable

[owp^3]

I have a form whose recordsource is a complex query.

The form populates fine but the status bar in the lower left flashes "this
recordset is not updateable" before it says calculating and form view. I am
unable to type in any of the fields on the form.

I searched this forum for advice and troubleshooting I have read Allen
Browne's page on the subject but i just don't seem to be able to find the
issue.
I have gone back and rebuilt the query in a string and unit fashion to
isolate the component causing the problem and I think I have isolated it but
don't know where to go from here.

This query is updateable:
SELECT select_BUInventoryPV.*, select_LastMRE.MRE_CurrentStatus
FROM select_BUInventoryPV LEFT JOIN select_LastMRE ON
select_BUInventoryPV.ID = select_LastMRE.MRE_ProjectID;

This query is not updateable
SELECT select_BUInventoryPV.*, select_LastUpdatedDPV.BT_Status
FROM select_BUInventoryPV LEFT JOIN select_LastUpdatedDPV ON
select_BUInventoryPV.BU_BT_Portfolio_ID =
select_LastUpdatedDPV.BT_Portfolio_ID;

All of the underlying queries are updateable.
This is the common underlying query
SELECT data_BUInventoryPV.*
FROM data_BUInventoryPV;

Here is the underlying query that bolixes things up
SELECT DPV.BT_Portfolio_ID, DPV.BT_Status, DPV.BT_Type, DPV.BT_Title,
DPV.BT_Description, DPV.BT_BU_Priority, DPV.BT_BU, DPV.BT_BU_Sponsor,
DPV.BT_BU_PM, DPV.BT_Financials, DPV.BT_BU_in_Plan, DPV.BT_BU_Planned_Target,
DPV.BT_Proposal_Received, DPV.BT_Project_Start, DPV.BT_Requirements_Signoff,
DPV.BT_Phase, DPV.BT_Phase_Due, DPV.BT_Committed_Flag, DPV.BT_Target_Install,
DPV.BT_Duration, DPV.BT_PSA_Risk_Category, DPV.BT_Health_Indicator,
DPV.BT_Executive_Status, DPV.BT_Manager, DPV.BT_Director,
DPV.BT_Project_Manager, DPV.BT_PF_BHP, DPV.BT_PF_Capital,
DPV.BT_PF_BT_Cross_Functional, DPV.BT_PF_Production_Assurance,
DPV.BT_Report_Date
FROM data_BTWeeklyPV AS DPV
WHERE (((DPV.BT_Report_Date)=(Select Max(X.BT_Report_Date)
FROM data_BTWeeklyPV as X
Where X.BT_Portfolio_ID=DPV.BT_Portfolio_ID)));

All queries except the last were built in the Query Design view not the SQL
View.
The last appears to work properly in all other cases. The datasheet view
looks correct and is updateable. Forms that use it as their sole record
source are updateable as well.

Is the problem the Aliases? Or the Subquery in the WHERE clause?

I think my work around is to populate another table with the results of the
second query and then query that table as part of my complex query, I will
try that in tomorrow morning. However, I sure would like to know how to
solve the problem with the query itself.

Thanks,

owp^3

 

[John W. Vinson]

The form populates fine but the status bar in the lower left flashes "this
recordset is not updateable" before it says calculating and form view. I am
unable to type in any of the fields on the form.

Here's the problem:

WHERE (((DPV.BT_Report_Date)=(Select Max(X.BT_Report_Date)
FROM data_BTWeeklyPV as X
Where X.BT_Portfolio_ID=DPV.BT_Portfolio_ID)));


No Totals query, nor any query containing any Totals operation - Max, Min,
Sum, Avg, etc. - is ever updateable - even if, as in this case, it logically
ought to be.

You can use

=DMax("[BT_ReportDate]", "[data_BTWeeklyPV]", "[BT_Portfolio_ID] = " &
[DPV].[BT_Portfolio_ID])

as a criterion instead of the subquery.

John W. Vinson [MVP]

 

[owp^3]

John,

Thanks so much for your reply.
I'm just not quite sure how to implement the change...

I get a repeating (I have to use Task Manager to end it) error message "Data
Mismatch" when I replace the subquery with the suggested formula like this:
WHERE ((DPV.BT_Report_Date)=DMax("[BT_Report_Date]", "[data_BTWeeklyPV]",
"[BT_Portfolio_ID] = " &
[DPV].[BT_Portfolio_ID]));

I am sure I am just doing it wrong. I have never used dmax before.
I've been concerned about the dreaded "performance" hit.

Thanks,

The form populates fine but the status bar in the lower left flashes "this
recordset is not updateable" before it says calculating and form view. I am
unable to type in any of the fields on the form.

Here's the problem:

WHERE (((DPV.BT_Report_Date)=(Select Max(X.BT_Report_Date)
FROM data_BTWeeklyPV as X
Where X.BT_Portfolio_ID=DPV.BT_Portfolio_ID)));


No Totals query, nor any query containing any Totals operation - Max, Min,
Sum, Avg, etc. - is ever updateable - even if, as in this case, it logically
ought to be.

You can use

=DMax("[BT_ReportDate]", "[data_BTWeeklyPV]", "[BT_Portfolio_ID] = " &
[DPV].[BT_Portfolio_ID])

as a criterion instead of the subquery.

John W. Vinson [MVP]

 

[John W. Vinson]

John,

Thanks so much for your reply.
I'm just not quite sure how to implement the change...

I get a repeating (I have to use Task Manager to end it) error message "Data
Mismatch" when I replace the subquery with the suggested formula like this:
WHERE ((DPV.BT_Report_Date)=DMax("[BT_Report_Date]", "[data_BTWeeklyPV]",
"[BT_Portfolio_ID] = " &
[DPV].[BT_Portfolio_ID]));

I am sure I am just doing it wrong. I have never used dmax before.
I've been concerned about the dreaded "performance" hit.

Well, you're sailing between Scylla and Charybdis here: if you need the latest
date and use a Subquery it won't be updateable, and if you use DMax you run a
(minor) risk of slower performance. Just try it and see.

The error message suggests that BT_Portfolio_ID is a Text datatype field. If
so you need the syntactically required quotemarks: try

WHERE ((DPV.BT_Report_Date)=DMax("[BT_Report_Date]", "[data_BTWeeklyPV]",
"[BT_Portfolio_ID] = '" & [DPV].[BT_Portfolio_ID] & "'"));

If that's not the problem, please post back with the datatypes and sample
contents of the relevant fields.

John W. Vinson [MVP]

 

[owp^3]

You are correct it is a text field. The query produces the expected
recordsheet results with the new syntax. Unfortunately, it does not resolve
the original issue.
The top level query is not updateable.

That got me thinking the underlying query select_LastMRE in the query that
works also has a WHERE clause with a select max subquery. It does seem to be
updateable even though it uses a Totals operation. Since aliases are used
with both "strings" that can't be the problem either.

In my original post I mentioned I was going to try to dump the results of
both underlying queries into temporary tables and query them directly without
any filtering criteria. This should circumvent all of the "complicated"
query issues.

Oddly, I get the same results. The data from select_BUInventoryPV and
select_LastMRE2 is updateable. The data from select_BUinventoryPV and
select_LastUpdatedDPV2 is not. Using the temp data tables (temp_MRE and
temp_LastUpdatedDPV) directly instead of intermediary queries produces the
same results. LastMRE is fine LastDPV is not.

I had not to this point defined any relationships between the tables. I have
since done that but that didn't help either.

So I wrote these queries looking at the base tables:
SELECT data_BUInventoryPV.BU_Title, history_MRE.MRE_CurrentStatus,
history_MRE.MRE_RptDate
FROM data_BUInventoryPV LEFT JOIN history_MRE ON data_BUInventoryPV.ID =
history_MRE.MRE_ProjectID;

SELECT data_BUInventoryPV.BU_Title, data_BTWeeklyPV.BT_Status,
data_BTWeeklyPV.BT_Report_Date
FROM data_BTWeeklyPV RIGHT JOIN data_BUInventoryPV ON
data_BTWeeklyPV.BT_Portfolio_ID = data_BUInventoryPV.BU_BT_Portfolio_ID;

The former is updateable the latter is not.

I don't see a difference in the properties of the tables. The foreign key
for history_MRE is NUMBER and the foreign key for BTWeekly is TEXT. That
shouldn't make a difference. Other than that the data for BTWeekly comes
from an Excel Import rather than captured within the application through a
form.

I am really stumped.

Any thoughts?

John,

Thanks so much for your reply.
I'm just not quite sure how to implement the change...

I get a repeating (I have to use Task Manager to end it) error message "Data
Mismatch" when I replace the subquery with the suggested formula like this:
WHERE ((DPV.BT_Report_Date)=DMax("[BT_Report_Date]", "[data_BTWeeklyPV]",
"[BT_Portfolio_ID] = " &
[DPV].[BT_Portfolio_ID]));

I am sure I am just doing it wrong. I have never used dmax before.
I've been concerned about the dreaded "performance" hit.

Well, you're sailing between Scylla and Charybdis here: if you need the latest
date and use a Subquery it won't be updateable, and if you use DMax you run a
(minor) risk of slower performance. Just try it and see.

The error message suggests that BT_Portfolio_ID is a Text datatype field. If
so you need the syntactically required quotemarks: try

WHERE ((DPV.BT_Report_Date)=DMax("[BT_Report_Date]", "[data_BTWeeklyPV]",
"[BT_Portfolio_ID] = '" & [DPV].[BT_Portfolio_ID] & "'"));

If that's not the problem, please post back with the datatypes and sample
contents of the relevant fields.

John W. Vinson [MVP]