Sorting subform records

[Remster]

Folks

The attached database contains a main form, PersonForm, and a subform, RelationshipSubform. PersonForm is bound to PersonTable, which contains a person ID field and a name field. RelationshipSubform is bound to RelationshipTable, which contains a relationship ID field and two person ID fields. How can I get the records in RelationshipSubform to sort by person name (the visible column in the combo box) when the subform is bound to a table that doesn't contain any person name fields?



Remster

[RuralGuy]

make the RecordSource of the SubForm a Query that joins the tables you want and then sort on whatever field in either table.

[vitols]

Folks

The attached database contains a main form, PersonForm, and a subform, RelationshipSubform. PersonForm is bound to PersonTable, which contains a person ID field and a name field. RelationshipSubform is bound to RelationshipTable, which contains a relationship ID field and two person ID fields. How can I get the records in RelationshipSubform to sort by person name (the visible column in the combo box) when the subform is bound to a table that doesn't contain any person name fields?

Remster

Hi

What about putting 'DLookUp' in a global function (ie in a module) called say MyDLookUp.
Then using MyDLookUp in a query or table lookup.

Vitols

[Remster]

make the RecordSource of the SubForm a Query that joins the tables you want and then sort on whatever field in either table.

I thought it might come to that! I've followed the spirit (if not the letter) of your advice and changed the form's Record Source to ...

SELECT Table1.Field1, ... , Table2.Field1, ... FROM Table1 INNER JOIN Table2 ON Table1.Field1=Table2.Field1

... which seems to work fine.

[RuralGuy]

You can specify an OrderBy clause on any field in there as well.