Solved Recordset Not Updateable (1 Viewer)

[tmyers]

Is there a method I can use to total related child records for the parent record without making the records not updateable? A totals query gives me what I am after, but you cannot update a totals query.

Long story short, I am adding cost modifiers to an original cost where the modifiers are in another table with a 1 to many relationship (1 base to many modifiers). Am I able to maybe do this totaling in a control on the form itself?

 

[Minty]

Can you left join the totals query to your base data - that should remain editable?

If not you can use a DSum or similar on the form but it generally makes them pretty slow to use.

 

[tmyers]

I did try making the total query as a left join on the dataset and unfortunately it was not updateable.
I thought about DSum but have had issues with slowness as you describe when doing many of them on a form.

 

[tmyers]

SELECT tblFixture.Type, tblFixture.Qty, tblFixture.Manufacturer, tblFixture.CatalogNo, tblFixture.Note, tblFixture.LampType, tblFixture.Sort, tblFixture.[Select], tblFixture.OverrideType, tblFixture.UnitCost, tblFixture.ID_Job, tblFixture.FixtureID, QryAdderTotals.TotalAdder
FROM tblFixture LEFT JOIN QryAdderTotals ON tblFixture.FixtureID = QryAdderTotals.ID_Fixture;

This is the query as is when trying to do the join.

Here is the totals query I am joining to the dataset:

SELECT tblFixtureAdder.ID_Fixture, Sum([AdderCost]*[AdderQty]) AS TotalAdder
FROM tblFixtureAdder
GROUP BY tblFixtureAdder.ID_Fixture;

 

[tmyers]

After messing around with it and reading up on what makes a query no longer able to be updated (which was a surprisingly long list), I determined it was best to not use a totals query and just use DSum in the control on the form. It is a little slower but I don't know if any other way to handle it.

The expression in the control is:
=[UnitCost]+Nz(DSum("TotalAdder","QryAdderTotals","ID_Fixture = " & [Forms]![frmJob]![frmQuoteContainer].[Form]![FixtureID]),0)

The totals query in #4 was changed to just a Select Query with the two fields being multiplied still but no longer be aggregated:

SELECT tblFixtureAdder.ID_Fixture, [AdderCost]*[AdderQty] AS TotalAdder
FROM tblFixtureAdder;

Here are the link(s) I read on what makes a query no longer able to be updated:

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

Why is my query not updateable?

I'm trying to build an updatable view in Access for a user. Basically, the underlying tables look like this: Accounts -------- accountId accountName accountHolder TransactionStatements ------------

stackoverflow.com

 

[Minty]

Are you using a SQL Server backend?

 

[tmyers]

I am not. Everything is within Access and the file is not yet split.

 

[Pat Hartman]

@tmyers Storing calculated values violates normal forms. You should calculate the values in a report rather than storing them.

 

[tmyers]

I don't believe I am. This was just for display on a control on one of my forms to calculate the totals and such, none of which I am storing but just displaying.

 

[Pat Hartman]

Great