Copy filtered recordset

[QueenKirsty]

I want to export data from forms to excel. I can do the export to excel bit if I can get a copy of the recordset.

I have a form with a filter (that the user can turn on and off) and I want to copy only the filtered data to the duplicate recordset but when I do:

Set rs =  Me.Controls("SubMiscSiteAccess").Form.Recordset

I get all the records not just the filtered ones. The filter being applied is:

me.filter = [sql statement here] 
me.filterOn

Any suggestions on how to get a copy of just the filtered records?

 

[vbaInet]

Hmm... not so sure. I thought the recordset would contain the filtered data. Maybe the filter is just applied on form level. Use this:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.Filter = Me.Filter

Use rst as your recordset.

 

[QueenKirsty]

brill! thanks vbaInet. Thank makes sense.

 

[vbaInet]

No worries. You're welcome.

 

[vbaInet]

Forgot to say, remember to clean up. Set the recordset object to nothing at the end.

Set rst = Nothing

 

[LPurvis]

Just to mention...

The clone of a recordset will return its currrent membership.
So if you have a filtered form, then the RecordsetClone property (or a Recordset.Clone method) will return a recordset of those filtered rows.

Consequently, once a form is filtered as you seemed to be doing from the outset in your original statement, you only need:
Set rst = Me.RecordsetClone
as offered, or:
Set rst = Me.Recordset.Clone
(to obtain a more limited scope object).

The line:
rst.Filter = Me.Filter
is redundant and, in an MDB (using a DAO recordset) would do nothing to the recordset at all anyway as a DAO recordset must be re-opened for an assigned filter property to be applied.

Just thought I'd mention that in case it wasn't clear to future readers which line had done the trick.

Cheers.