Sort Order for Records in Subforms

[The Professor]

Hi,



I have a One to Many relational database, based on two Tables (Customers and Invoices). One Customer Record can be linked to many Invoice Records.

The Invoices Table is displayed in a Subform on the Customer Form. As you browse through the Customer Records, the Invoice Records for the relevant customer are displayed in the Subform - this part works very well. Obviously, the Subform displays only one Invoice Record at a time, but as each customer can have several or more invoices, my issue is this - how do I determine in what order the Invoice Records are displayed in the Subform?

For example, if a customer has 20 invoices, (only the first of which will be displayed in the Subform) how do I force them to be displayed in a particular order as I cycle through them? I'd like them to be displayed in date order (using the Date field) showing the latest Invoice Record first. But equally they could be sorted by the Invoice Number field, starting with either the lowest or highest number first - if only I knew how it could be done. Currently they appear to be just random in their sort order. The Invoices Table behind the Subform is sorted by the Invoice Number field, but this isn't reflected in the order in which they are being displayed in the Subform.

There doesn't seem to be a Property for the Subform Control that I can set, that will determine in what order the Records for the Subform will be sorted. I'd really appreciate some help with this if anyone has some ideas.

Prof.

[June7]

Sort order is managed by the subform container SourceObject. Is the SourceObject a table, query, or form? They all have an Order By property. Otherwise, use a query or form with a query as RecordSource and apply sort criteria within the query.

[pbaldy]

You should be able to use the Order By property, but I'd base the subform on a query that included the sorting you wanted. You know the subform can display multiple invoices if you want?

[The Professor]

Hi June7,

The SourceObject is a Table. I have now found the OrderBy property, but there no 'dropdown list' nor 'expression builder' for that property, so what do I put in there? A field? If so, (say a date field) how do I tell it (in the OrderBy property) to sort hi - lo, or A - Z etc?

[orange]

1)As Paul said you could use a query on the table with an Order By.

2)To use the table, try

Order By ="YourieldName Desc" .... the DESC is descending, omit it if you want ascend which is default.