I'm trying to set the recordset of a subform on a mainform based on criteria entered by the user. I first create a DAO recordset to make sure that the criteria that the user has entered returns at least 1 result but no more than 65,533 records. I then can apply the rs to the form using:
Set Me.subResults.Form.Recordset = rs
Set rs = Nothing
This works, the problem is that then I can't set the Order By property then since (I think) the rs is based a query.
I've also tried setting the RecordSource property and the Filter property:
The problem with that is that the query without the filter would return around 1 million records, and as soon as I set the RecordSource it tries to retrieve every one, which takes too many minutes (where just doing the filtered query takes anywhere from less than a second to ten seconds, acceptable).
Any suggestions how to still let the user Order the records without making them wait for minutes on end?