Requery Button Not working after Applying a form filter (1 Viewer)

gdfarrell · Sep 12, 2008

I have a form that has a requery button and a separate form filter button. The form has two unbound fields that are referenced in the forms Query criteria. When I first enter the form without applying filters, the requery button accurately refreshes the form based on the values entered into the unbound fields.

The code behind the Requery button is:

Private Sub Refresh_Click()

If Me.FilterOn = True Then
Me.FilterOn = False
DoCmd.RunCommand acCmdRemoveFilterSort
End If

DoCmd.Requery
DoCmd.GoToRecord acDataForm, "TRIPHeader", acLast

End Sub

After applying the filter button (which executes some specific filtering)

Private Sub Qlikview_Filter_Click()

Me.Filter = "TRIP_ID in (SELECT TRIP_ID FROM QlikviewList)"
DoCmd.RunCommand acCmdApplyFilterSort

End Sub

I then want to go back to the Requery button the Query criteria (stored in the Query itself) does not take effective. All possible rows from the form query are returned even though my unbound fields in the form are referenced in the query criteria. I have tried all sorts of combinations of removing the filter but I still seems that any time a filter is applied and then after it is removed the Forms Query Criteria is no longer functioning properly. Any help would be creatly appreciated on this.

Additionally I have also found that if i click on the Remove Filter icon on the Filter/Sort toolbar the above Requery Button works fine so it appears that there is something different happening when I use the toolbar Remove Filter vs when i use the DoCmd.RunCommand acCmdRemoveFilterSort orMe.FilterOn = False commands

wazz · Sep 12, 2008

hi gd. your question is really confusing but maybe you need
me.filteron = false

Code:

[B]TIP[/B]

In a Microsoft Access database (.mdb), the Refresh method shows only 
changes made to records in the current set. Since the Refresh method 
doesn't actually requery the database, the current set won't include 
records that have been added or exclude records that have been deleted 
since the database was last requeried. Nor will it exclude records that no 
longer satisfy the criteria of the query or filter. To requery the database, 
use the Requery method. When the record source for a form is requeried, 
the current set of records will accurately reflect all data in the record 
source.

gdfarrell · Sep 12, 2008

I am using the Requery command which does requery the database not the Refresh method. I just called the button a refresh button for the users.

Would you know why the Remove Filter Icon on the Filter sort menu does not behave like the DoCmd.RunCommand acCmdRemoveFilterSort or the Me.FilterOn = False.

wazz · Sep 12, 2008

i'm trying to figure this out too, i don't use filters much at all.
one thing, what if you change DoCmd.Requery to me.requery.
requery needs an object to work on.

as for the difference b/t the toolbar and the code command, not sure, i haven't found a difference yet.

any one else with ideas plz jump in. let us know if me.requery helps. tnx.

MagicMan · Sep 12, 2008

I have found a number of the toolbar commands do act differently when dispatched from VB. Even when you use a sendkey equivilant to Pressing a hotkey for requery.
The Filter is especialy tempramental as to when it is removed, a requery actually executes prior to the filtr remove when stacked awaiting execution. The way I got around this problem, after many days and many experiments, was to forget about the filter. I recreate the rowsource/recordsource/controlsource for the object in vb and use the me.object.xxxxxxxsource = the new select with the filter in the select. This causes a requery with the new source.

If anyone knows why there is a difference (If there actually is), I would really like to know....please.

gdfarrell · Sep 12, 2008

Hi WAZZ,

Thanks for the input but the me.Requery behaved the same as the Docmd.Requery. I still need to click the icon to effectively remove the filter and allow the query to behave normally.

wazz · Sep 12, 2008

one thing i found while experimenting is that this:
?forms!frmMyForm.filter
gave me the actual filter that shows up on the form's prop sheet. (data tab -> filter)

what if you set:
forms!frmYourForm.filter = ""
then, if necessary (prob is nec):
forms!frmYourForm.recordsource = your orig query
then, if nec:
me.requery

i remember now why i don't use filters. (although i have used me.filteron = false a few times with no probs.........................).

gdfarrell · Sep 12, 2008

Wazz,

You headed me in the right direction by putting...

Me.FilterOn = False
forms!frmYourForm.recordsource = "My Query"
Docmd.Requery

This basically emulates the Remove Filter icon.Thanks for your help on this.

gdfarrell · Sep 15, 2008

Here is some more information .....

I found that simply using

forms!frmYourForm.recordsource = "My Query"
Docmd.Requery

Removed the filter from the form and sped things up as well.

MagicMan · Sep 15, 2008

Excellent, that is exactly where I was pointing you. Glad you got good results.

And Welcome to the forum.

Smiles
Bob

If anyone knows why...." I have found a number of the toolbar commands do act differently when dispatched from VB. Even when you use a sendkey equivilant to Pressing a hotkey for requery." .... Please let me know.

mdlueck · Jun 23, 2011

gdfarrell said:
I found that simply using

forms!frmYourForm.recordsource = "My Query"
Docmd.Requery

THANK YOU for posting this solution! I have a Form based on a Query. I need to update the Query (SQL) in order to specify a different sort column. The Query was updating, but the Form UI was refusing to update. The Query in the database was even being updated.

I found in my case I did not have to hard code the Form name into the code.

Code:

    'Save the Query
    Set db = CurrentDb()
    db.QueryDefs("qrylocalJDEItemInfo").SQL = strQuery
 
    'Update the Form UI with the updated Query
    Me.RecordSource = "qrylocalJDEItemInfo"
    Me.Requery

Now the Form UI updates (sort order) based on which column label control is clicked.

gemma-the-husky · Apr 8, 2015

I had this same problem today, and found this very useful tip, which I am sure I wouldn't have found by trial and error.

filter by selection, and then undo filter is very powerful and useful, and it would have been a shame to lose it on this particular form

gemma-the-husky · Apr 8, 2015

just another update on this

my original query used a value from the form header to restrict the items returned, and this in conjunction with the removefilter displayed the problem.
(where somefield = form!somevalue)

i just changed the query to use a function rather than the form value
(where somefield = somefunction)

hey presto, worked correctly and no need to reset the query. I actually think it was quicker with no screen flicker. Maybe access was getting confused with multiple filters from the same form.

Minty · Oct 26, 2016

Massive thread resurrection, but i'm experiencing this and saw your function solution, but can't seem to get it to work.

I was originally passing a listbox value into my query. I've replaced this with a simple function that takes the listbox as a control and returns its value. But I get a expression is to complex to evaluate error on opening the form.

The query is saved and works fine until I replace the criteria with the function. Running the function in the immediate window works, and there is a default value set for the list box.

Code:

Public Function ListContract(lstBox As Control) As Long

ListContract = lstBox.Value

End Function

misscrf · May 5, 2017

gdfarrell said:
Wazz,

You headed me in the right direction by putting...

Me.FilterOn = False
forms!frmYourForm.recordsource = "My Query"
Docmd.Requery

This basically emulates the Remove Filter icon.Thanks for your help on this.

This was exactly what fixed my issue, and I can't thank you enough!!!

Requery Button Not working after Applying a form filter (1 Viewer)

gdfarrell

New member

wazz

Super Moderator

gdfarrell

New member

wazz

Super Moderator

MagicMan

Registered User.

gdfarrell

New member

wazz

Super Moderator

gdfarrell

New member

gdfarrell

New member

MagicMan

Registered User.

mdlueck

Sr. Application Developer

gemma-the-husky

Super Moderator

gemma-the-husky

Super Moderator

Minty

AWF VIP

misscrf

Registered User.

Similar threads

Users who are viewing this thread