Form Filter/Order By -- Bug? or Operator Error?

[Tom]

All,

I have a record review form (with multiple subforms) that isn't
sorting as expected when I filter it.

It's a form used to review contract line item details by fiscal year.
Each CLIN record has a bunch of detail controls plus subforms
containing required deliveries, contract modifications affecting that
CLIN, etc.

The relational hierarchy is CONTRACTS_ID > FISCAL_YEARS_ID > CLINS_ID

[multiple tables & subforms linked to their parent CLIN by

CLINS_ID].

Note that CLINS_ID is a long integer primary key that often does not
sort the same as the alphanumeric CLIN itself since CLINs get added
after the fact. You might see something like the following:

CLINS_ID CLIN
1297 0042AD
1298 0042AE
1299 0004AT <-- added later, out of sequence

When I filter the form to display only CLIN records for a particular
fiscal year, it does, in fact, filter the records displayed down to
the requested FY. However, they are no longer in the same sort order
as they are when not filtered.

I attempted to control the sort order with an Order By statement:

CONTRACTS_ID, FISCAL_YEARS_ID, CLIN (the alphanumeric CLIN descriptor,
not the CLINS_ID key)

What do I need to do in order to display the filtered CLIN records in
alphanumeric order on the form?

Thanks,

Tom

 

[John W. Vinson]

What do I need to do in order to display the filtered CLIN records in
alphanumeric order on the form?

Preferably, use a query sorted on that field as the Recordsource of the form;
or, set (in code) the form's OrderBy property to the CLIN field, and its
OrderByOn property to True.

John W. Vinson [MVP]