Automatically Refreshing Pivot Tables in Access

[Sharks2431]

Hey all. I posted this over at Mr. Excel too on the Access forums, but I thought maybe someone from this site might know the answer to my question.

I know pivot tables and charts are more up Excel's ally, but my question has more to do with Access I think. I've made a pivot table form from a query and everything is working fine except one thing. I want the table to refresh automatically. I don't want the user to have to go in and refresh the data manually.

I'm thinking I could put something on the forms "On Open" event but I don't know the specific coding. If anyone has any idea on this, I'd greatly appreciate some feedback.

 

[Moniker]

In the form's AfterUpdate event, put this:

Me.Requery
Me.Refresh

That will refresh it after each and any change is made to the form.

 

[Sharks2431]

Sorry for the delay. I tried using Me.Requery and Me.Refresh in the afterupdate event (as well as a few other events as well) but that refresh the pivot table. Let me explain a little more clearly. I have a form that people enter data into. I have a query tied to this form that displays the top 10 occurences of a specific field (supplier). Next I have a pivot table form that I've made from the query.

Lets say the user inputs 10 more of Supplier X into the form and X is in the top 10. The form obviously updates and the query updates too, but in order to update the actual pivot table, I have to go in and right click the table and click the refresh data button. I don't want the user to have to do this.

Hopefully that makes things more clear. I appreciate the help.

 

[Moniker]

Access the pivot table by its object name and use the Refresh method directly on the object instead of the form. For example, if your pivot table object is called "pvtSuppliers", then pvtSuppliers.Refresh is what you would use.