Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005

    Unanswered: Setting Recordset of Subform

    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:
    Me.subResults.Form.RecordSource = "qryName"
    Me.subResults.Form.Filter = strWHERE
    Me.subResults.Form.FilterOn = True
    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?
    Me.Geek = True

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    Perhaps you could try something like this

    Me.subResults.Form.RecordSource = "SELECT * FROM qryName WHERE " & strWHERE

    Just a thought


  3. #3
    Join Date
    May 2005
    Hey Mike,

    Thanks for the suggestion, it worked like a charm! I should have thought of trying that.
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts