Djblois
01-03-2007, 06:07 PM
If you read my first post I got the form added with this code:
LC = "ItemCode"
No = "NoItemNumber"
SBI = "SortbyItem"
SBP = "SortbyProduct"
PivotTableOptions.Controls.Add bstrProgId:="Forms.Frame.1", Name:=LC, Visible:=True
PivotTableOptions.Controls(LC).Top = 157
PivotTableOptions.Controls(LC).Left = 6
PivotTableOptions.Controls(LC).Height = 70
PivotTableOptions.Controls(LC).Width = 210
PivotTableOptions.Controls(LC).Caption = "Do you want Item# in the Report?"
PivotTableOptions.Controls(LC).Font.Bold = True
PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=SBP, Visible:=True
PivotTableOptions.Controls(SBP).Top = 14
PivotTableOptions.Controls(SBP).Left = 12
PivotTableOptions.Controls(SBP).Height = 14
PivotTableOptions.Controls(SBP).Width = 120
PivotTableOptions.Controls(SBP).Caption = "Yes, Sort by Product"
PivotTableOptions.Controls(SBP).Font.Bold = True
PivotTableOptions.Controls(SBP).GroupName = Itemcode
PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=SBI, Visible:=True
PivotTableOptions.Controls(SBI).Top = 38
PivotTableOptions.Controls(SBI).Left = 12
PivotTableOptions.Controls(SBI).Height = 14
PivotTableOptions.Controls(SBI).Width = 120
PivotTableOptions.Controls(SBI).Caption = "Yes, Sort by Item#"
PivotTableOptions.Controls(SBI).Font.Bold = True
PivotTableOptions.Controls(SBI).GroupName = Itemcode
PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=No, Visible:=True
PivotTableOptions.Controls(No).Top = 27
PivotTableOptions.Controls(No).Left = 140
PivotTableOptions.Controls(No).Height = 14
PivotTableOptions.Controls(No).Width = 30
PivotTableOptions.Controls(No).Caption = "No"
PivotTableOptions.Controls(No).Font.Bold = True
PivotTableOptions.Controls(No).Value = True
PivotTableOptions.Controls(No).GroupName = Itemcode
Unfortunately my checkboxes do not recognize the controls I added. here is the code that I use:
'Add Fields to Pivot Table
On Error Resume Next
If PivotTableOptions.NoItemNumber.Value = True Then
pt.AddFields RowFields:=Array("Customer", "Cust#", "Product"), ColumnFields:="Date"
pt.PivotFields("Cust#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Product").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
End If
If PivotTableOptions.SortbyProduct.Value = True Then
pt.AddFields RowFields:=Array("Customer", "Cust#", "Product", "Item#"), ColumnFields:="Date"
pt.PivotFields("Cust#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Product").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Item#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
End If
If PivotTableOptions.SortbyItem.Value = True Then
pt.AddFields RowFields:=Array("Customer", "Cust#", "Item#", "Product"), ColumnFields:="Date"
pt.PivotFields("Cust#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Product").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Item#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
End If
LC = "ItemCode"
No = "NoItemNumber"
SBI = "SortbyItem"
SBP = "SortbyProduct"
PivotTableOptions.Controls.Add bstrProgId:="Forms.Frame.1", Name:=LC, Visible:=True
PivotTableOptions.Controls(LC).Top = 157
PivotTableOptions.Controls(LC).Left = 6
PivotTableOptions.Controls(LC).Height = 70
PivotTableOptions.Controls(LC).Width = 210
PivotTableOptions.Controls(LC).Caption = "Do you want Item# in the Report?"
PivotTableOptions.Controls(LC).Font.Bold = True
PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=SBP, Visible:=True
PivotTableOptions.Controls(SBP).Top = 14
PivotTableOptions.Controls(SBP).Left = 12
PivotTableOptions.Controls(SBP).Height = 14
PivotTableOptions.Controls(SBP).Width = 120
PivotTableOptions.Controls(SBP).Caption = "Yes, Sort by Product"
PivotTableOptions.Controls(SBP).Font.Bold = True
PivotTableOptions.Controls(SBP).GroupName = Itemcode
PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=SBI, Visible:=True
PivotTableOptions.Controls(SBI).Top = 38
PivotTableOptions.Controls(SBI).Left = 12
PivotTableOptions.Controls(SBI).Height = 14
PivotTableOptions.Controls(SBI).Width = 120
PivotTableOptions.Controls(SBI).Caption = "Yes, Sort by Item#"
PivotTableOptions.Controls(SBI).Font.Bold = True
PivotTableOptions.Controls(SBI).GroupName = Itemcode
PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=No, Visible:=True
PivotTableOptions.Controls(No).Top = 27
PivotTableOptions.Controls(No).Left = 140
PivotTableOptions.Controls(No).Height = 14
PivotTableOptions.Controls(No).Width = 30
PivotTableOptions.Controls(No).Caption = "No"
PivotTableOptions.Controls(No).Font.Bold = True
PivotTableOptions.Controls(No).Value = True
PivotTableOptions.Controls(No).GroupName = Itemcode
Unfortunately my checkboxes do not recognize the controls I added. here is the code that I use:
'Add Fields to Pivot Table
On Error Resume Next
If PivotTableOptions.NoItemNumber.Value = True Then
pt.AddFields RowFields:=Array("Customer", "Cust#", "Product"), ColumnFields:="Date"
pt.PivotFields("Cust#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Product").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
End If
If PivotTableOptions.SortbyProduct.Value = True Then
pt.AddFields RowFields:=Array("Customer", "Cust#", "Product", "Item#"), ColumnFields:="Date"
pt.PivotFields("Cust#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Product").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Item#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
End If
If PivotTableOptions.SortbyItem.Value = True Then
pt.AddFields RowFields:=Array("Customer", "Cust#", "Item#", "Product"), ColumnFields:="Date"
pt.PivotFields("Cust#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Product").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
pt.PivotFields("Item#").Subtotals = Array(False, False, False, False, False, False, _
False, False, False, False, False, False)
End If