Sort records in form problem

[Mihail]

Hello !

I have 2 tables:

tblParents:
    ID_Parent (autonumber)
    ParentName (Text)

tblChilds:
    ID_Child (autonumber)
    ID_Parent (Look Up field on tblParents)
    ChildName

and one form, frmChilds, bound to tblChilds
In this form, a combo box (named ID_Parent)
The Row Source is:

SELECT [tblParents].[ID_Parent], [tblParents].[ParentName] FROM tblParents ORDER BY [ParentName];

and the Control Source is ID_Parent

The question is:
How to open this form and have sorted it's records by ParentName by default ?

Thank you !

[NeoPa]

If the form is for [tblChilds], but the sort order required is for [ParentName] (in [tblParents]), then you need to base the form on a record source that includes [ParentName]. IE. You need to link the two tables together (on [ID_Parent]) and use the result for the Record Source of the form.

[Mihail]

The tables are linked as you say. ID_Parent is a FK in tblChilds.
You say that the solution is to bound the form to a query (or use custom SQL as the Record Source) ?

Thank you for reply !

[NeoPa]

Yes (almost).

The solution is to bind the two tables together in a query. Custom SQL is also a query (which is technically different from a QueryDef - which is an Access Saved Query object). When I said you need to link the tables together, I meant to link them within a query (using a JOIN statement). The resultant query, whether QueryDef or custom SQL, would be the record source of the form.

[Mihail]

Thank you !