Sort Mainform based on earliest date on subform?

[CazB]

I have a form which shows training events - these can take place over one or several days, and can be run by one trainer or several, so I have my basic Events Info in one table (EventID, EventType, Location, that kind of thing) and the 'Jobs' in a separate table (EventID, JobDate, TrainerID, etc)

The problem I'm having is that I want the List of Events to be sorted in order of their start date... which is on the subform, not the main form.

How can I go about it - and still leave both the main and subforms fully editable? :banghead:

 

[pr2-eugin]

Set the ORDER BY either in the RecordSource or in the Form Design?

 

[CazB]

Can I set the MAIN FORM ORDER BY to be a field off the subform?

 

[pr2-eugin]

SubForm's have their own Recordsource, as similar to that of the Main form, so I would go with the answer Yes !

 

[CazB]

maybe I'm being thick, but I can't see how?

Just to clarify:

I want the Events on the Main form to be sorted by the 'Earliest Date' on the related Jobs which are on the subform... even though the dates aren't ON the main form.

 

[jdraw]

Can you show us a jpg of your tables and relationships?
Typically Form/subForm are related 1 to Many. One record on the main form may have 1 or many related records on the subform.

Just saw your response to Paul when submitting.
If the field you want to sort by is not in the recordsource ---then can not sort by it.

 

[CazB]

These are my relationships...

If I can't sort on something that's not on the mainform, then could I use a function within the main form's record source to 'lookup' the earliest date from the subform?
.. and if I could, could someone please help me to write the function? pretty please

( there are a lot more tables in my db but these are the only ones I need for this scenario )

 

[jdraw]

CazB,

Please open your tables in the jpg to show all fields.

 

[CazB]

The only 'invisible' ones are LastUpdateDate and LastUpdateBy in all 3 tables.

 

[pr2-eugin]

So what is your Form Hierarchy? What is your SubForm and MainForms? In general, MainForm would be the Form bound to the One sided table..

Why is Event Start date not available on the main table?

 

[CazB]

I was trying not to store it twice.... there's nothing to stop me adding it in, but it means it'd be stored in both the Events table and the TrainerCalendar....

The Main form is based on the Event, with the subform based on the trainer calendar so it can show who is running the course on which date.

I'll see if I can do a stripped down version and upload it - might be easier!

 

[CazB]

ok that was going to take too long - there would've been 8 years of data to delete!

so this is what the form looks like

 

[David R]

This takes two queries... one to determine what the Minimum (uncompleted) Event Date is for each trainer, then one to patch that info onto your main form so it can be used in ORDER BY. No functions or code necessary.

Be careful, it's easy to make your main form non-updateable doing this.