Delete all set fields in pivot table Access 2010 (1 Viewer)

[jaryszek]

Hello,

i have a code which is adding fields into pivot table based on user choice from continous form:

Private Sub Polecenie11_Click()

Dim fst1 As Object
Dim frm1, frm2 As Object
Dim i As Long
Dim rst As DAO.Recordset
Dim coll As New Collection
Dim FieldSety As Variant

On Error GoTo Koniec

'Open form in PivotTable view and set
'a reference to the form
Set frm2 = Forms.Item("tbl_Name_PivotForm")

''reference to curent continous form recordset
Set rst = frm2.Recordset

'Set PivotTable fieldsets


'check what user has choosen and this to collection
rst.MoveLast
rst.MoveFirst

Do While Not rst.EOF
    If IsChecked(frm2!Wybór) Then
        coll.Add frm2!Nazwa_Pola.Value, frm2!Nazwa_Pola.Value
    End If
rst.MoveNext
Loop

If coll.Count = 0 Then
    MsgBox "Wybierz chociaż jedno pole do tabeli przestawnej"
    Exit Sub
End If

'' open form with pivot

DoCmd.OpenForm "BrakiForm_Pivot", acFormPivotTable
Set frm1 = Forms.Item("BrakiForm_Pivot")

'' for each item from collection add new pivot table field

For Each FieldSety In coll
    Set fst1 = frm1.PivotTable.ActiveView.FieldSets(FieldSety)
    frm1.PivotTable.ActiveView.DataAxis.InsertFieldSet fst1
    ''frm1.PivotTable.ActiveView.DeleteFieldSet fst1
Next FieldSety

''frm1.PivotTable.ActiveView.DeleteFieldSets

''Call ClearPivot(coll, frm1)

DoCmd.Close acForm, Me.Name

Exit Sub
Koniec:
MsgBox "Błąd danych, skontaktuj się z administratorem " & Err.Number & Err.Description


End Sub

Code is working but now i would like to add code to clear all fieldsets within pivot table (before adding new ones).

How can i achive it ?

Best wishes,
Jacek

 

[Ranman256]

if you build the pivot from sql, there are no fields to remove, only what you create.
Here the user picks the item from a combo box to run pivot on:

'---------------
Private Sub btnXtab_Click()
'---------------
Const kQry = "qxXtab"

MakeXtabSql kQry
DoCmd.OpenQuery kQry
End Sub

'---------------
Private Sub MakeXtabSql(ByVal pvQry)
'---------------
Dim sSql As String
Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs(pvQry)

sSql = "TRANSFORM Sum(qsXtab0." & cboSet & ") AS SumOfFld " & _
"SELECT qsXtab0.Dept, '" & cboSet & "' AS DataSet, Sum(qsXtab0." & cboSet & ") AS [TotalOf " 

& cboSet & "] " & _
"FROM qsXtab0 " & _
"GROUP BY qsXtab0.Dept, '" & cboSet & "'" & _
"PIVOT Format([ProdDate],'yyyy-mm');"

qdf.SQL = sSql
qdf.Close
Set qdf = Nothing
End Sub

 

[jaryszek]

Wow thank you ! I didn't know that you can build pivot from SQL.

Can i read a little more about creating pivots from SQL commands somewhere in web?

It can be very useful for future.
Best wishes
Jacek

 

[jaryszek]

Ok i did the work using additional code:

For Each FieldSetyPivot In frm1.PivotTable.ActiveView.DataAxis.FieldSets
   Set fst1 = frm1.PivotTable.ActiveView.FieldSets(0)
   frm1.PivotTable.ActiveView.DataAxis.RemoveFieldSet FieldSety
Next FieldSetyPivot

this code is looping through all visible fields within pivot and deleting them

Best Wishes
Jacek Antek