By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

No records after a filter is set the second time.

P: 8
I have VBA code (Access 2003) like this:

Private Sub SubName()
rs.Filter = ""
x = msgbox("Filter cleared. Number of records: " & rs.RecordCount")
rs.Filter = "SELECT * from [Table]"
rs.Sort = "Field1 ASC, Field 2 DESC"
x = msgbox("Filter set to " & rs.Filter & ". Number of records: " & rs.RecordCount")
End Sub


The recordset and connection are declared in the Declarations section of the form. Everything I'm doing is on the same form.

The FIRST time this sub is run, it works great. Any time after that, I get the full recordset when the filter is cleared, but no records when the filter is set again.

Any ideas?
Dec 6 '07 #1
Share this Question
Share on Google+
6 Replies


puppydogbuddy
Expert 100+
P: 1,923
I have VBA code (Access 2003) like this:

Private Sub SubName()
rs.Filter = ""
x = msgbox("Filter cleared. Number of records: " & rs.RecordCount")
rs.Filter = "SELECT * from [Table]"
rs.Sort = "Field1 ASC, Field 2 DESC"
x = msgbox("Filter set to " & rs.Filter & ". Number of records: " & rs.RecordCount")
End Sub


The recordset and connection are declared in the Declarations section of the form. Everything I'm doing is on the same form.

The FIRST time this sub is run, it works great. Any time after that, I get the full recordset when the filter is cleared, but no records when the filter is set again.

Any ideas?
Sheree,

I think you need to turn the filter on and off, in addition to settting the filter's value, Try it this way:

Private Sub SubName()
rs.Filter = ""
rs.FilterOn = False
x = msgbox("Filter cleared. Number of records: " & rs.RecordCount")
rs.Filter = "SELECT * from [Table]"
rs.FilterOn = True
rs.Sort = "Field1 ASC, Field 2 DESC"
x = msgbox("Filter set to " & rs.Filter & ". Number of records: " & rs.RecordCount")
End Sub
Dec 6 '07 #2

P: 8
FilterOn is a property of the form (Microsoft Documentation).
My recordset is not part of the form, and does not have a FilterOn property. It is declared like this:

Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Private Sub Form_Load()
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
End Sub
Dec 6 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
FilterOn is a property of the form (Microsoft Documentation).
My recordset is not part of the form, and does not have a FilterOn property. It is declared like this:

Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Private Sub Form_Load()
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cn
End Sub

Sheree,

Ok since you have the documentation handy... how about the ApplyFilter method? If that is not valid method, then the only other thing that comes to mind is to use Null instead of "" when clearing out your filter...
Dec 6 '07 #4

P: 8
That didn't seem to do much of anything.

I even switched it around and pointed to my table as a data source in the GUI, using "Me.Recordset" instead of 'rs', in order to expose the .FilterOn property... no dice. It won't filter at all.
Dec 6 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
Sheree,
Ok, I did some checking ....this is what I think will fix your problem:

Setting a recordset's Filter property to adFilterNone restores the original recordset with which you were working:

So change:
rs.Filter = " "
To:
rs.Filter = adFilterNone

Also, you need to move the first two lines
rs.Filter = adFilterNone
x=...........................

to the last two lines of your sub. ....the reason being is that logically you would clear the filter and restore the original recordset after executing your rs.filter statement, not before.

Let me know what happens. Thanks.
Dec 6 '07 #6

P: 8
I had already tried using the adRecordsetNone constant. It didn't work any better than .Filter = "".

For a completely unrelated reason (needing to move the backend database to different locations to take it from home to work, etc.), I decided to use VBA to link my tables... and when that was completed, the original code, with no modification, started working flawlessly.

I guess I'm totally stumped by that one, but whatever... It works.

Oh, and for the record, the reason why the filter gets cleared at the beginning and not at the end, is because the subroutine is linked to a "search" button... meaning after I use the button to get my filtered records, i still have the filtered records until the next thing needs to cancel the filter, so my rs.Filter = "" is fine where it is, kthx.
Dec 6 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.