ProgrammingMSAccess.com

The ProgrammingMSAccess.COM Site

A vba Procedure with three called procedures that create a new form with a designated name, assign a record source to the new form, and generate a pivot chart on the form.

'Dim strFormName As String
Dim strRecordSource As String

Private Sub cmdDefaultColumnChart_Click()
Dim strDefaultName As String

'Create an empty form with a PivotChart
'default view and a record source
strRecordSource = "ExtendedPricesSummedByProduct"
strDefaultName = CreatePivotChart

'Rename the form from its default name
strFormName = "pvcDefaultColumn"
If (AssignPivotChartName(strDefaultName, _
strFormName)) = False Then
Exit Sub
End If

'Configure the PivotChart
ConfigureDefaultColumnChart

End Sub

Function CreatePivotChart() As String
Const acFormPivotChart = 4
Dim frm1 As Access.Form

'Create an Access form and assign
'it a PivotChart default view
Set frm1 = CreateForm
frm1.DefaultView = acFormPivotChart
frm1.RecordSource = strRecordSource

'Return default form name and save form
CreatePivotChart = frm1.Name
DoCmd.Close acForm, CreatePivotChart, _
acSaveYes

End Function

Function AssignPivotChartName _
(strDefaultName As String, _
strFormName As String) As Boolean
Dim acc1 As AccessObject
Dim acc2 As AccessObject

'Assign value of True by default
AssignPivotChartName = True

'Search for existing form with new form
'name and if one is found
' 1. save up to two backup copies
' 2. query before erasing second backup copy
' 3. exit
For Each acc1 In CurrentProject.AllForms
If acc1.Name = strFormName Then
For Each acc2 In CurrentProject.AllForms
If acc2.Name = strFormName & _
"oldbkup" Then
If vbNo = MsgBox("OK to " & _
"erase old backup form?", _
vbYesNo) Then
MsgBox "Consider " & _
"using a new name " & _
"for pivot chart " & _
"form or manually " & _
"assigning a new " & _
"name to old " & _
"backup form"
AssignPivotChartName = False
DoCmd.DeleteObject acForm, _
strDefaultName
Exit Function
End If
DoCmd.DeleteObject acForm, _
strFormName & "oldbkup"
DoCmd.Rename strFormName & _
"oldbkup", acForm, _
strFormName & "bkup"
DoCmd.Rename strFormName & _
"bkup", acForm, strFormName
Exit For
ElseIf acc2.Name = strFormName & _
"bkup" Then
DoCmd.Rename strFormName & _
"oldbkup", acForm, _
strFormName & "bkup"
DoCmd.Rename strFormName & _
"bkup", acForm, strFormName
Exit For
ElseIf acc2.Name = strFormName Then
DoCmd.Rename strFormName & _
"bkup", acForm, strFormName
Exit For
End If
Next
Exit For
End If
Next acc1

'Rename the form from its default
'value to the value of strFormName
DoCmd.Rename strFormName, acForm, _
strDefaultName

End Function

Sub ConfigureDefaultColumnChart()

'Open form in PivotChart view and set
'a reference to the form
DoCmd.OpenForm strFormName, acFormPivotChart
Set frm1 = Forms.Item(strFormName)

'Assign sets of column values to chart
'categories and chart values
With frm1.ChartSpace
.SetData chDimCategories, chDataBound, _
"ProductName"
.SetData chDimValues, chDataBound, _
"ExtendedPrice"
End With

'Suppress field buttons as well as assign
'and format chart and axis titles
frm1.ChartSpace.DisplayFieldButtons = False
With frm1.ChartSpace.Charts(0)
.HasTitle = True
.Title.Caption = "Sales by Product"
.Title.Font.Size = 14
.Axes(0).Title.Caption = "Products"
.Axes(1).Title.Caption = "Sales ($)"
End With

'Close form with its PivotChart view
DoCmd.Close acForm, frm1.Name, acSaveYes

End Sub

Want to understand  Microsoft Access 2000/2002/2003 so that you can program it to do more tasks like this?  Get Programming Microsoft Office Access 2003 by Rick Dobson from Microsoft Press.  Learn more about the book by clicking here.

Copyright 2003 CAB, Inc. All rights reserved. Republication or redistribution of CAB, Inc. content, including by framing or similar means, is expressly prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be liable for any errors in the content, or for any actions taken in reliance thereon.