Pivot style read/write in forms

[Mr. Smith]

Hi.

I want to try to do this in a form:

Load data into a pivotstyle layout.

Let user change values and save values down to the pivottable source.



I have trouble finding a way to let users edit data in pivotstyle layout,
and save it back to the database.



I've even thought about building it from scratch with hiding/showing text
boxes, and move them around (up/down) to simulate expand/collapse.



The scenario is 28 rows in 8 columns, where the 28 rows should be summed up
around 6 total groups.



Is there a way to do this efficient in Access 2007 or will I have to set up
224 text boxes and build a sophisticated TopValue generator that will be
set the TopValue on the text boxes, based on a "expand/collapse" icon....





ANY hints appreciated.....



Kind regards

Mr. Smith

 

[DStegon via AccessMonster.com]

You can create a table teh wil store the pivot name and the XLM data. Then
when opening the pivot give them the ability to pull from the table and load
the schema

frm.PivotTable.XMLData = rst!Pivot_XML
frm.ChartSpace.XMLData = rst!chart_XML
Dim ppt As PivotTable
Set ppt = frm.ChartSpace.InternalPivotTable
ppt.Refresh

Table can be structured like this "tbl_Pivot_View"
!Pivot_View_ID
!Pivot_ID
!Schema_Name
!Pivot_XML
!Chart_XML

To save the current pivot data call a function like this

Public Function SavePivot()
Dim rst As New ADODB.Recordset
Dim PivotData As String
Dim ChartData As String
Dim frm As Pivot_Type
On Error Resume Next
PivotData = Screen.ActiveForm.PivotTable.XMLData
ChartData = Screen.ActiveForm.ChartSpace.XMLData
frm = Screen.ActiveForm.PivotType

DoCmd.OpenForm "frm_Pivot_Save", , , , , acDialog, "form=" & frm
If Not IsLoaded("frm_Pivot_Save") Then GoTo exithere
With rst
.Open "SELECT * FROM tbl_Pivot_View WHERE schema_name = " & FixQuotes
(Forms("frm_Pivot_save").txtSchema), CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
If .RecordCount = 0 Then
rst.AddNew
Else
If FL_frm_msgbox("Are you certain that you want to over-write
this definition?", vbYesNo + vbCritical, "Confirm") = vbNo Then
msgbox "Definition not saved.", vbOKOnly + vbExclamation,
"Aborted"
GoTo exithere
End If
End If
!schema_name = Forms("frm_Pivot_save").txtSchema
!Pivot_XML = PivotData
!chart_XML = ChartData
!Pivot_ID = frm
If Not rstUpdate(rst) Then GoTo exithere 'YOU CAN CALL A REGULAR
UPDATE..
.Close
End With

exithere:
DoCmd.Close acForm, "frm_Pivot_save"
rst.Close
Set rst = Nothing
Exit Function

End Function


Hope that helps.

 

[Mr. Smith]

DStegon
Thank you so much for spending your time on my issue, so quickly. I really
appreciate it.

I'll look into your approach which looks very thorough and sophisticated.

Thanks a lot!

Kind regards
Mr. Smith