Access Pivottable

[spoonjy]

Hi,
I have built a pivot table using VBA, and some of the codes are listed below,

With frm1.PivotTable.ActiveView
.RowAxis.InsertFieldSet .FieldSets("Client name")
.RowAxis.InsertFieldSet .FieldSets("Orders")
(I have problem when a second rowaxis is inserted)

.ColumnAxis.InsertFieldSet .FieldSets("Money")

.DataAxis.InsertFieldSet .FieldSets("Price")

.AddTotal "Total Money", .FieldSets("price").Fields("price"), plFunctionSum

.DataAxis.InsertTotal .Totals("Total Money")

End With
Screen.ActiveDatasheet.PivotTable. _
ActiveData.HideDetails
(This is to show the summarised version)

what i get is, The dataaxis field is summarised, but the FIRST RowAxis(Client Name) is in "expand" mode. i have to manually click on the "Collapse" button on the tool bar in order to see the overall summarized version.

how to "Collapse" the "Client Name" fieldsets using VBA?
thanks in aniticipation.

[RobDog888]

Record a macro doing the action you desire. Then stop and check out the module code generated by Excel to give you an insight in how its done.

[spoonjy]

HI, thanks for the reply,
i know there is a record macro in EXCEL.
but i am doing it in Access. and there is no record macro.
besides, the codes generated by excel for this action does not work for ACCESS.

so may i know if any of you have the code for this action.
thanks