No records after a filter is set the second time.

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

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

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...

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.

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.

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.