Accessing a forms filtered resultset in a query (1 Viewer)

I have a large table. I am making a form that displays the table, and allows the user to use Access' standard filters on many different fields to choose only some of the data. Then, after the user has used the filters to choose only some of the data, I want to be able to include only the resulting recordset (and not the entire original table) in various queries.

I'm not sure if there's a way I can directly reference the form's underlying recordset in a query.

I tried iterating through the entire form's recordset and dumping it into a new table, and then using that table, but this is incredibly slow on large datasets.

Here is the code I used:


Is there an alternate way of doing this?

you have a continuous form showing all records
user enters data into the search boxes, then filter the records shown....

Test all controls for a possible filter then build the where clause.

'unclick the filter button (on menu bar) to remove it and show all records again.

Ranman256 said:

you have a continuous form showing all records
user enters data into the search boxes, then filter the records shown....

Test all controls for a possible filter then build the where clause.

Code:Copy to clipboard

 if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
     'remove 1st And
sWhere= mid(sWhere,4)
   'just use the filter
 me.filter = sWhere
me.filterOn = true

'unclick the filter button (on menu bar) to remove it and show all records again.

Click to expand...

That's not exactly what I was looking for. The form is in Datasheet view, and I really wanted the user to be able to use all the versatile filtering options available in datasheet view.

However, you gave me an idea. I checked the Filter property of the form during runtime, and I see that it has a beautiful Where clause that I can use in a query. So thank you very much!