Cannot Update When Using a Query for a Form's Record Source

[whdyck]

I'm using Access 2003.

This might be a bonehead question, but is it possible to use a multi-table query for a form's Record Source and still be able to update records in one of the tables?

I have the following query as the Record Source for a form:

[FONT=Courier New]SELECT tblLoading.LoadingID,[/FONT]
[FONT=Courier New]    tblLoading.ClaimPartyID,[/FONT]
[FONT=Courier New]    tblLoading.ContractNumber,[/FONT]
[FONT=Courier New]    tblLoading.PoolID,[/FONT]
[FONT=Courier New]    tblLoading.Tonnes,[/FONT]
[FONT=Courier New]    tblLoading.LastUserName,[/FONT]
[FONT=Courier New]    tblLoading.LastUpdateDtm,[/FONT]
[FONT=Courier New]    qryClaimPartyWithChildTotals.ClaimPartyAmount,[/FONT]
[FONT=Courier New]    qryClaimPartyWithChildTotals.TotalLoadingTonnes[/FONT]
[FONT=Courier New]FROM   qryClaimPartyWithChildTotals[/FONT]
[FONT=Courier New]      INNER JOIN tblLoading ON qryClaimPartyWithChildTotals.ClaimPartyID = tblLoading.ClaimPartyID[/FONT]

The table to update is tblLoading, and in my form I'm currently only displaying fields from tblLoading. (But I'd like to add the other fields for display-only later.) However, when I use this query as my Record Source, I cannot update the record in the form. (All fields seem to be read-only.)

Can this be done?

Thanks for any help you can give.

Wayne

 

[Galaxiom]

Generally a form/subform structure is the solution to these problem.

 

[pbaldy]

Depends on the query:

http://allenbrowne.com/ser-61.html

 

[whdyck]

Generally a form/subform structure is the solution to these problem.

Not sure how that will help me. I'll give a few more details.

Form frmLoading (see attached) currently displays the calculated field "Amount USD" by pro-rating the "Actual Amount" on the parent record (or the "Estimated Amount" if no "Actual Amount") by the Tonnes on the current record compared to the SUM of all Tonnes. So for the first record in the attached, Amount USD = $10,795.52 = $23,459.72*(11000/23904.085).

Until now, I've just used tblLoading as the Record Source (rather than a query) and done the rest with calculated fields. But since we need these calculated amounts in a report, I'd rather just have a single query that works for both the form and the report. Is this even possible?

Thanks again.

Wayne

 

[Galaxiom]

I expect the problem is that qryClaimPartyWithChildTotals is an Aggregate (aka Totals) Query which makes the query not updateable.

You probably just need to use a different query for the form and report.