We help IT Professionals succeed at work.

Access Continuous Form only upudates by closiing and reopening

john madigan
john madigan asked
on
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,
Comment
Watch Question

Distinguished Expert 2017

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

Author

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,
Distinguished Expert 2017

Commented:
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.
Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:
<<Forms![Home].[Form].Requery>>

 Correct syntax is:

 Forms![Home].Requery

or when in the form:

 Me.Requery

Jim.

Author

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?

Author

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.

Author

Commented:
Could this be an issue with a continuous form?  Do I need to put in some code to close then open it?
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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

Author

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,

Author

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, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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