Subform Calculated Field Used on Main Form to Sort Records

[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!

 

[Allen Browne]

You will need to get the total into the main form somehow.

One approach would be to make a query that uses both tables (the one from
the main form, and the one from the subform.) You can then depress the total
button (on the toolbar in query design view), to sum the amount. This gives
you the total in the main form, so you can sort on it. While this will
execute swiftly, it will give you a read-only main form.

Another approach would be to create a query using just the main form's
table, and type a DSum() expression into the field row to get the total
owed. Make this query the RecordSource for your form, and you can sort on
the field. It will be slow to execute if you have more than a few hundred
records.