To sort the records in the form based on a calculated field, you need to add the calculated field to the result set in the Record Source property. We've provided a download to demonstrate how this can be done.
We've created a form called frmAccounts that lists all records in the Accounts table. We've then created a textbox called TotalUnits that displays the number of records in the Account_units table (for the account displayed on frmAccount).
Open the form in Design view and select Properties under the View menu. Select the property called Record Source. A button with three dots should appear to the right of this property. Click on this button.
When the Query Builder appears, select all of the fields from the Accounts table and then enter the function for the calculated field. In this example, we've entered the following:
CalcUnits([AcctNo])
Next, select how you've like to sort the calculated field. We've chosen to sort in Descending order.
You can close down the Query Builder. When prompted with the message box, "Do you want to save the changes made to the SQL statement and update the property?", click on the Yes button.
Now when you open the form, you'll see the form records sorted in descending order based on the "Total Units" field.
