Sort within a subform?

[dasadler]

I have a master form & a subform with continuous records. The subform has the rowsource as a query that sorts the results by last name yet when the data is displayed in the subform, it is not sorted. How can I make that data sort?

[HansV]

Normally, a form or subform should use the sort order of its record source.
Open the subform in design view.
Activate the Data tab of the Properties window.
Has the Order By property been set? If so, clear it and save the subform.

[dasadler]

The order by is blank. If I wanted to enter something there for sort order, would I enter the field of the Query that should be sorted? - Nope - tried that and was prompted for the field value when I opened the subform.

[HansV]

You should preferably leave the Order By property blank, but if you get a parameter prompt when you enter a field name, you have a problem. Check carefully that you have set the Control Source of the subform to the correct query.

[dasadler]

I am working with the original 'Course & Students" DB referenced in post 3502. When I am looking at the course form, the students taking the course are not sorted although the qryStudents is sorted correctly. Everything seems to be set correctly. The subform seems to sort on StudentID from the Student table.

[HansV]

That is because the record source of the subform is not set to a query but to the Participation table.
If you want the subform to be sorted, you must set the record source of the subform to a query.
See the attached version.

DASM2MTest.zip

(I think you were looking at the row source of the combo box - that is a query and the dropdown list is sorted correctly.)

[dasadler]

You're correct - that is what I was looking at. I made the necessary changes and it works. I need to understand better about rowsource because I don't understand why it works... at this point, I am simply happy that it does.

[HansV]

A form (whether a main form or a subform) has a Control Source. This is the name of a table or query, or a SQL statement that returns the records to be displayed by the form.

A combo box and a list box have a Row Source. This is the name of a table or query, or a SQL statement that returns the records to be displayed in the list.

So if you have a combo box on a form, you need to distinguish between the Control Source of the form (it determines which records are displayed by the form) and the Row Source of the combo box (it determines what is displayed in the dropdown list of the combo box).

[dasadler]

thanks Hans, that helps.