Query Refresh problem

[Paul_Christie]

Ken

I'm back on this issue. The code for refreshing queries on a dropdown is working fine and runs fast enough. The dependant queries are refreshing when that dropdown is triggered so it all looks fine. All the tables have got additional columns with formulae and the Preserve Columns sort/filter/layout option is ticked.

Now I've got one depot that has 44 lines of data and one that has 33 lines of data. If I select the one that has 44 lines it does everything ok. If I then got to the one with 33 lines it does that ok. If I then go back to one with 44 lines of data some of the formulae columns lose their formulae on the bottom 11 records. If it was consistent I might not find it so frustrating.

The query that is showing the problem is a KPI summary query so the actual query is returning roughly 10 columns of data on a 'group by' against 13,000 records and there are about 20 added columns. it's strange because the 11 rows that lose their formulas don't lose them all. The first 5 will be ok then it misses 7, sets 3 or 4 more ok then misses say 6 and then does the rest ok.

A part of me always wanted to implement this as a template solution so that the user only worked on one Depot in a given workbook but you know what users are.

Any thoughts on what I'm missing?

Paul

 

[Paul_Christie]

Ken

I've just done a test to see if your function UpdatePowerQuery worked as opposed to my method of using ListObject.QueryTable.Refresh BackgroundQuery:=False and they both behave in the same way.

I've also checked all the columns with equations to see if I could find any pattern in those that did copy down versus those that didn't and I couldn't see a pattern. I thought perhaps it might have something to do with those formulae that referred to the current table and those that referred to other tables and there was no discernable pattern.

Paul

 

[Paul_Christie]

Ken

Another case of answering my own question. I went back to a post that you'd made on technet where you suggest deleting the added columns, and re-adding them. So I copied all the formulae over to another workbook, documentation now done , removed the columns added to the table, turned the Preserve Columns option off and then on again and re-added the columns and formulas. Success, it now refreshes properly.

Paul