Re-Query RecordSource after using me.filter=""

[bignose2]

Hi,

I have a continuous form.

It is mostly filtered via a text box at top, ie. I type a surname
The Recordsource Query (although SQL in the RS & not sep query)
then filters the form from the query that has
Like [Forms]![MainForm]![surname] & '*'
works fine.
I also have a search textbox to find anything else on the form & a variety of other searches
This uses

Me.Filter = "([RegID] = " & Me!RegID & ")"
or
DoCmd.ApplyFilter , Me.Filter = "([RegID] = " & Me!RegID & ")"

I use a variety, I know me.Filter = .. is better than docmd but both work & sometimes neater.

This again is fine & they both work seperately but when I want to reset the form I can never properly refresh the main query to change the surname. it clear the list but never filter after, I type a name & the list remains full.

I set surname = ""
requery
refresh
For the query
me.filter = ""
me.filteron = false
to clear any extra filters.
I think the filter part is OK but I suspect if a separate query and I was to apply by name it might work.

On the form, rather than unfiltered (which it show or filtered) it now shows No Filter.

Simply can't get it to work.

tried me.filteron=true just so it is on with no filter set, requery & many combinations

I did change the recordsource sql but got very messy with so many different fitlers I wanted to use on the form and when I changed the table it was a real pain changing all the RS everywhere.

I am thinking having default RS and setting this but again trying to get way from that.

Any ideas how to simply requery the mainform

 

[arnelgp]

on the first part of your post, you mention recordsource, does this mean you modify the recordsource sql by appending the filter?

if so you can, clear the filter and reinstate the original query, ie:


Dim strOriginalQuery as string

Private Sub Form_Load()
' save original query
strOriginalQuery = Me.Recordsource
End Sub


then to reset all filtering:

Me.Filter=""
Me.FilterOn=False
Me.Recordsource = strOriginalQuery
'you may elect to requery if you want
'Me.Requery

 

[bignose2]

Hi,

Thanks for your reply

No, I am now trying not to change the record source, I did like the way it works & had everywhere but whenever I make fundamental changes in design view I then have to be extra careful that when resetting back to default/original afterwards.
I might re-think back to this, It was just making sure that the default RS was always as I had designed rather than on the fly. ie. if it crashed out or exited whilst the RS was still on a different filter settings.
I might be over complicating this but not sure of the potential for ...
Private Sub Form_Load()
' save original query
strOriginalQuery = Me.Recordsource
End Sub
... to actually not get or as designed RS.

I think if default set when the program starts rather than just form load btu still feel might go wrong somewhere.

I was actually setting default me.Recordsource = "SELECT ......" the whole SQL, copying from the underlying query but as I say needed in lots of places & if changed.

I don't quite understand why requery does not work and simply go back to what is in the design but it does not.

 

[bignose2]

Hi,

Follow up to my previous, it seems I have missed a very fundamental point that I was not aware of in all my years (Idiot)

I thought that Me.Recordsource was changed perminantly. I realise now it gets reset when the form closes or I guess when is loads.

That does make life a lot easier, I was always worried about that.

Thanks for you advise as it is much better to keep using the recordsource for filtering in many situations.

regards