Subform Calculated Field Used on Main Form to Sort Results

[David Newbold]

Hope someone can help...

I have a subform called [Charge Information]. Within that form is a field
called [Chg Owed Amount]. I added a text box called [Total Owed] which sums
up the [Chg Owed Amount] fields that are listed.

The main form is called [Account Demographics]. On the main form I added a
text box called [Total Owed Main Form] which has the following as it's
control source =[Charge Information].Form![Total Owed]. This works in order
for me to have the total owed amount appear on the main form.

Now the problem...

I want to be able to sort the presentation of the main form from high to low
total owed amount. However, since the field on the main form is derived it
is not a field that MS-Access is allowing me to use for sorting the records
tied to the main form.

Any ideas as to how I can get that total owed amount value in such a way
that I can use it to sort the main form records?

Thanks!

 

[Dirk Goldgar]

Hope someone can help...

I have a subform called [Charge Information]. Within that form is a field
called [Chg Owed Amount]. I added a text box called [Total Owed] which
sums
up the [Chg Owed Amount] fields that are listed.

The main form is called [Account Demographics]. On the main form I added
a
text box called [Total Owed Main Form] which has the following as it's
control source =[Charge Information].Form![Total Owed]. This works in
order
for me to have the total owed amount appear on the main form.

Now the problem...

I want to be able to sort the presentation of the main form from high to
low
total owed amount. However, since the field on the main form is derived
it
is not a field that MS-Access is allowing me to use for sorting the
records
tied to the main form.

Any ideas as to how I can get that total owed amount value in such a way
that I can use it to sort the main form records?

You'd have to revise form's recordsource query to calculate and include the
value. You can't do it by referring to the form and suibform, though.
Instead, you have to either

(a) join your main form's base table to the related table that includes the
charge information, and turn the query into a totals query so you can sum
the [Chg Amount Owed] field,

or

(b) just add a calculated field that uses the DSum() to get the total owed
for each main record.

Approach (a) is most efficient, but would render the query non-updatable.
So if you need to be able to edit or add records on the main form, you'd
need to use approach (b).