Access Continuous Form only upudates by closiing and reopening

I have a continuous form called "HOME" that the record source is a query called "qryReviews".  The query shows RevieID, ClientID

When I put in a value for ClientID in the Criteria of the query - then save & run the query - the query show only records where ClientID = 5 - so that works.

But when I go back to my HOME form and click refresh all it still shows all the records.  If I x-out of the HOME form and then open it up then it shows the records that are showing in the query - ClientID = 5.

Not sure why the form is not updating after i run the underlying query and select Udate All  - and why I am having to reload the HOME form to show the correct records?

Any suggestions - Thanks,
john madiganAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Refresh does not rerun the query.  It simply "refreshes" the existing recordset so you will see any updates or deletes (they will say #deleted#).  It will not even pull in new records because it works only with the existing recordset.

If you changed the criteria, you want to rerun the query so you will need to use the Requery method.  Many people mistakenly use these interchangeably so you'll see lots of bad advice regarding them on the internet.  One other thing to keep firmly in mind is that if you Refresh or Requery a form, Access will save the current record if it is dirty.  Again many people tell you to use Refresh or Requery to save a record.  That is incorrect.  Although the record does get saved, both can have unexpected side effects such as repositioning a continuous or DS form.
john madiganAuthor Commented:
I put in a button on the form and with this code:

Forms![Home].[Form].Requery

So I go to the query and put in "5" for the ClientID Certeria - run the query - it returns back only records with ClientID = 5

Now I go back to the HOME form and click my button - it runs the
Forms![Home].[Form].Requery
but all the records are still showing.

if I exit out of the form then come back in the records on the form are now filtered.

Could I have some property on the form set up wrong?

Thanks,
PatHartmanCommented:
Personally, I rarely allow multiple forms to be open (visible) at one time.  It simply confuses the user and causes issues like this.  If you want to leave the second form open, then the code in the first form is going to have to check to see if the second form is open and if it is, close it and then reopen it.  No requery would be necessary.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Forms![Home].[Form].Requery>>

 Correct syntax is:

 Forms![Home].Requery

or when in the form:

 Me.Requery

Jim.
john madiganAuthor Commented:
I have only 1 form called "HOME"  - I have one query called "qryREVIEWS" that is the Record Source for the HOME form.  I don't have 2 forms.

The qryREVIEWS determines which records will show on the form.  When I add a number to the criteria of ClientID = 5 - and I run the query it returns just the records for ClientID = 5.  Now I save the query - close the query - go back to my form and do a requery of the form - it does not change

But if I close out of the form and open it again it shows the filtered records.

So for some reason when I do a Requery on the form it is not pulling the data from the filterd qryREVIEWS query - unless I close the form and reopen it.

I have done this a bunch of times before - can't figure out what the issue is?

Any suggestions?
john madiganAuthor Commented:
I changed my button to Forms![Home].Requery - still have the same problem - only will change to reflect filtered query if I close and open form.
john madiganAuthor Commented:
Could this be an issue with a continuous form?  Do I need to put in some code to close then open it?
Dale FyeOwner, Developing Solutions LLCCommented:
From what I can tell, you have a form (Home) and you have that form open displaying all of the records.

You then (which frmHome is still open), open the query that is the recordsource for the form edit it with a criteria, run the query, and it returns the results you want.  It sounds like you are then saving the query, with this changed criteria.  Is that correct?  If you reopen the query at this point and re-run it, did it save properly (with the criteria) or not.

I believe that your problem is that once you load frmHome, with that query as the recordsource, changes you make to the query itself will not be visible in the form, ever, because the SQL for that recordsource is already loaded in the form, so even if you requery the form, it will still return the original recordset.

However, you could use:

Forms("yourFormName").Recordsource = "yourQueryName"

This causes Access to reassign that query as the recordsource of the form, and will return the correct recordset.

However, why don't you simply filter the form with the new criteria:

me.Filter = "[ClientID] = 5"
me.filterOn = true

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
john madiganAuthor Commented:
your suggestion using the me.Filter worked - it is filtering the HOME form correctly.

I put textbox on the form [txtClientFilter]  that I can put in a number so my code is:

    Me.Filter = "[ClientID] = " & Me.txtClientFilter
    Me.FilterOn = True

Now how would I use the me.Filter ....        to remove any filter so I can see all the records?  Do I just change the filter to False?

Thanks,
john madiganAuthor Commented:
just found this to take off the filter

DoCmd.RunCommand acCmdRemoveFilterSort

it works great now - I can filter the form.

Thanks for your help - always better to keep it simple.
Dale FyeOwner, Developing Solutions LLCCommented:
I usually just add a button with the clear filter image and in the Click event of that button:
Private Sub cmd_ClearFilter_Click

    me.filter = ""
    me.filteron = false

End Sub

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.