Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-07-2018, 03:36 PM   #1
ria4life
Newly Registered User
 
Join Date: Feb 2016
Posts: 39
Thanks: 13
Thanked 0 Times in 0 Posts
ria4life is on a distinguished road
Filtering Recordset

Id like to filter thisr ecordset with a value from a form...however...the filter does not seem to do anythong...please advise.



PHP Code:
Function getCalendarData() As Boolean
Dim rs 
As DAO.Recordset





    Set rs 
CurrentDb.OpenRecordset("qry_employeeAttendance"dbOpenDynaset)
              
rs.Filter "EmployeeID = [Forms]![FRM_Main]![FRM_Welcome]![user]" 

ria4life is offline   Reply With Quote
Old 12-07-2018, 03:42 PM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,166
Thanks: 0
Thanked 692 Times in 677 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Filtering Recordset

You don't filter recordsets.
Filter a form with a query.
Ranman256 is offline   Reply With Quote
Old 12-07-2018, 03:43 PM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,591
Thanks: 62
Thanked 1,210 Times in 1,110 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Filtering Recordset

The problem may be WHEN you apply the filter.

In this article:

https://docs.microsoft.com/en-us/off...r-property-dao

Quote:
You can use the Filter property to restrict the records returned from an existing object when a new Recordset object is opened based on an existing Recordset object.
(Underscoring in that quote was something I added.)

If you open the recordset first, I think it is then too late to apply the filter.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 12-07-2018, 04:29 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,218
Thanks: 10
Thanked 3,903 Times in 3,846 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Filtering Recordset

I'd do this:

Code:
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qry_employeeAttendance WHERE EmployeeID = " & [Forms]![FRM_Main]![FRM_Welcome]![user], dbOpenDynaset)
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-07-2018, 04:54 PM   #5
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 235
Thanks: 3
Thanked 37 Times in 36 Posts
theDBguy is on a distinguished road
Re: Filtering Recordset

Hi,

Just to clarify your intention. Must you open an unfiltered recordset and then subsequently apply a filter to it? Or would opening a filtered recordset to begin with, as has already been suggested, be sufficient?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 12-07-2018, 04:59 PM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,472
Thanks: 40
Thanked 3,384 Times in 3,279 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Filtering Recordset

Most developers would use Paul's suggest but you can assign a filtered recordset to a new recordset (or itself). Something like

Code:
 
Dim rst As DAO.Recordset
Dim db As DAO.Database
 
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM someTable")
rst.Filter="ID<=200"
set rst=rst.openrecordset
However there is no going back, once filtered, the records filtered out are lost - unless you assign to a difference recordset

Code:
 
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim Frst as DAO.Recordset
 
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM someTable")
rst.Filter="ID<=200"
set Frst=rst.openrecordset
then you can still go back to your unfiltered rst recordset. However any changes you made in Frst will not be reflected in rst.

I guess it can have it's uses but good practice is to bring the minimum number of records through in the first place.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filtering a recordset with a variable mjdemaris Modules & VBA 4 10-08-2015 01:39 PM
Filtering Recordset gmatriix Visual Basic 4 02-27-2013 08:30 AM
recordset filtering suburbanpsyco Modules & VBA 6 06-09-2011 04:00 AM
Recordset Clone Filtering issue derekroger Modules & VBA 10 05-30-2010 09:55 PM
recordset help - filtering?? branston Modules & VBA 3 06-03-2009 07:10 AM




All times are GMT -8. The time now is 05:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World