old data when refreshing a pivot table linked to a ms query with a parameter

Question

So I have a ms query with a parameter (let's call it "qry_Accounting") and I linked this parameter to a field that is linked to a combobox. This way I can filter my data with the combobox.

I then created a Pivot Table based on the ms query. Normally, I should be able to see the data linked to the item selected in the combobox. I can see that when I change the item selected in the combobox the data changes in "qry_Accounting". I had to create a button, so that when I change the item selected and click on the button it will refresh the pivot table.

My problem is I always have the data from the previous selected item in my pivot table, and only that data. I really don't understand what happens. If I create a new pivot table it will have the correct data, but the behaviour continues when I change the item, I still always keep the previous data and I don't get the new data.

One more thing, If I manually refresh the table it will work. It will work even for multiple pivot tables when I refresh one of them if they are all linked to the same qry_Accounting table.

How can I have the correct data showing without asking the user to manually refresh all the time?

PS : I already changed the property "Number of items to retain per field" to "none".

Following some questions in the comment here is more of my code (When clicking on the button to apply the combobox change) :

Sub Button5_Click()

    'If the buffer place (O4) is different from the combo linked cell (F4) then      
    'assign the value from F4 to O4.
    'the table will then be updated because as soon as O4 value changes the    
    'table is updated (the parameter is taken from O4)

    If Sheets("base_pivot").Range("O4").Value <> Sheets("base_pivot").Range("F4").Value Then
        Sheets("base_pivot").Range("O4").Value = Sheets("base_pivot").Range("F4").Value
        'We wait 5 seconds to avoid access denied problems
        Application.Wait (Now + #12:00:05 AM#)
        'we ask for a refresh of the pivot tables explicitely
        RefreshPivotTables ActiveWorkbook
        'we ask for a refresh of all data.
        ActiveWorkbook().RefreshAll
    End If

End Sub

Sub RefreshPivotTables Code

Sub RefreshPivotTables(wb As Workbook)

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim pf As PivotField

    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            pt.PivotCache().Refresh
            pt.RefreshTable
        Next
    Next
End Sub

Other than that everything is automatic. The ms query has a parameter, so as soon as the O4 value is updated, the new data is loaded in the table. So then the only thing to do it to refresh the pivottable.

Answer 1

So in the end I changed the way I do things since I couldn't make it work with MS query. I am now using excel 2016 query and no parameter anymore. The file is bigger, it is slower, but it works (I have over 100k lines in my data). I am using a slicer to filter the table. I am using VBA to update the slicer value to the value in the combo box. This way the pivottable get the correct data all the time. It seems pivot table has a problem when using MS query with a parameter, at least that is what I get from this experience. I'll probably try again in the future with another document.

Thanks for your help Shai Rado, I learned quite a few things a pivot tables and their cache :)

Answer 2

Since I don't have your query ("Table_edu_ana_invoices_query4"), I tested the code below using an Access DB, and just wrote the Query in the code below (you will see in the code comments).

Let me know if you figure out how to modify the Query section to your needs.

Code

Sub RefreshPivotTables(wb As Workbook)

Dim ws As Worksheet
Dim pt As PivotTable
Dim ptCache As PivotCache
Dim pi As PivotItem
Dim pf As PivotField

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmdCommand As ADODB.Command

'===== modify here to put your Connection String to your DB =====
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Radoshits\Desktop\Database11.accdb"

'===== this is the String query I am using >> replace with yours =====
rs.Open "SELECT * FROM Table1", con, adOpenStatic, adLockReadOnly

For Each ws In wb.Worksheets

    For Each pt In ws.PivotTables
        ' update Pivot Cache with Query settings
        With pt.PivotCache
            Set .Recordset = rs
            .Refresh
        End With

    Next pt
Next ws

Set rs = Nothing
con.Close
Set con = Nothing

End Sub