Query a form's recordset 1

[jmstarbuck]

I have a form that the user is allowed to "filter" in various ways. I accomplish this by creating a query based on their selections, using that query to open a recordset to make sure it returns rows and then setting the form's record source equal to that query text.

 dim r as object
 r.Open FilterSelectText, CodeProject.Connection, adOpenStatic
    GENNumRecs = r.RecordCount
   
   If (r.EOF And r.BOF) Then
      MsgBox ErrMessage, vbOKOnly, "NO DATA"
   Else
      filtertext = filterSelectWhere
      Me.RecordSource = FilterSelectText
      Me.Requery
      'Set Me.Recordset = r
      Me!recordsetinfo = "(of " & GENNumRecs & ")"
   End If

At first I was working with actual filtering but I ran into problems, got frustrated, and changed my approach. This seems to work, but I am open to suggestions.

Now that I have the form using the desired recordset, I would like to create various combo boxes listing data from that recordset in various orders.

Currently, I am setting the rowsource of the combo boxes = to a query incorporating the form's record source query. The problem is that the query includes multiple sub queries and is slow. Is there a way to simply query agaist the form's current recordset when creating my combo boxes?

Any guidance would be appreciated.

J

 

[MajP]

Here is a demo.
I return the forms recordset.
I set the combo's recordset = the forms recordset.
I put a different order on the combo's recordset.
I put a different filter on the combo's recordset.

So the combo is sorted and filtered differently but starts with the forms recordset. Now this works with DAO, but since you bound the form to an ADO recordset I am not sure if the form can return a DAO recordset. I think not. If you can use a DAO recordset for your form then this will work

Public Sub setComboToRS()
  Dim cmb As Access.ComboBox
  Dim rs As DAO.Recordset
  Dim strOrder As String
  Dim strFilter As String
  Set cmb = Me.Combo3
  'set the combo recordset to the forms recordset
  Set cmb.Recordset = Me.RecordsetClone
  'put a different order on the comb0
  strOrder = "A DESC"
  Set rs = cmb.Recordset
  rs.Sort = strOrder
  'reopen the recordset
  Set rs = rs.OpenRecordset
  'reset the combo to the new recordset
  Set cmb.Recordset = rs
  'put a filter on the combo recordset
  strFilter = "A = 'Mary'"
  rs.Filter = strFilter
  Set rs = rs.OpenRecordset
  Set cmb.Recordset = rs
End Sub

This may not be more efficient, but this technique can be really helpful especially when you have no idea of the filter or sort order the user may have applied to the form using the built in menu items.

 

[MajP]

I did the filter and sort seperately for demonstration purposes only. It would just be
....
rs.Sort = strSort
rs.filter = strFilter
set RS = rs.openrecordset