Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Including and excluding series in a Pivot Chart (2003)

    I'm trying to make it so that the user could choose whether to display or not series in a pivot chart view of a subform.
    On the startup form of the attached mdb, please set the "begins" date to March 1, 2004 and the "ends" date to March 31, 2004, select the "Flow" option button and click the Chart button.
    The form that opens has three button, one for each series so that, by clicking it, the user decides whether to display it or not.
    Clicking on any of the three buttons gives the error message:
    <font color=red>Object doesn't support this property or method.</font color=red>
    and the offending code line is:
    <font color=448800>With frm1.PivotTable
    .ActiveView.ColumnAxis.FieldSets("Portata galleria"). _
    Fields("Portata galleria").IncludedMembers = Empty </font color=448800>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    In the first place, you should have

    Set frm1 = Me.frmPortate.Form

    instead of

    Set frm1 = Me.frmPortate

    In the second place, when the form is opened, frm1.PivotTable.ActiveView.ColumnAxis.FieldSets is empty, there is no FieldSets("Portata galleria").

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    Thank you Hans,
    I've run the sub below to ascertain what the fieldset name is and it gives me "Testo122" so I've substituted it into Sub Comando1_Click() and now, when I press the "Galleria" button, I get the message:
    Subscript out of range

    Sub UtilityToNameFieldSetMembers(strFName As String)
    Dim frm1 As Access.Form
    Dim fset1

    'Open a form named strFName
    DoCmd.OpenForm strFName, acFormPivotTable
    Set frm1 = Forms(strFName)

    'Enumerate names of elements in the Fieldsets collection
    'This is useful for compound names, such as Shippers_CompanyName
    Debug.Print frm1.PivotTable.ActiveView.FieldSets.Count
    For Each fset1 In frm1.PivotTable.ActiveView.FieldSets
    Debug.Print fset1.Name
    Next fset1

    'Clean up objects
    Set frm1 = Nothing

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    When the error occurs, I type

    ? frm1.PivotTable.ActiveView.ColumnAxis.FieldSets.Co unt

    in the Immediate window, and get 0 as result. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    Which element of the code do you think is the subscript that's out of range?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    Since FieldSets.Count = 0, the FieldSets collection is empty, hence any reference to a member of this collection will result in "Subscript out of range".

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    Because they belong to the DataAxis, not the ColumnAxis.

    I think the If Then logic is flawed. I think you intended this - I also applied consistent indentation to make the code more readable.

    Private Sub Comando1_Click()
    Dim frm1 As Form
    Dim strFormName As String

    Set frm1 = Me.frmPortate.Form

    'Include one, two, or three shippers
    If Comando1.Tag = "n" Then
    With frm1.PivotTable
    .ActiveView.DataAxis.FieldSets ("Testo122") _
    .Fields("Testo122").IncludedMembers = Empty
    .AllowFiltering = False
    End With
    Comando1.Tag = "y"
    ElseIf Comando1.Tag = "y" Then
    With frm1.PivotTable
    .ActiveView.DataAxis.FieldSets("Testo122") _
    .Fields("Testo122").IncludedMembers = _
    Array("Federal Shipping", "Speedy Express")
    .AllowFiltering = False
    End With
    Comando1.Tag = "n"
    End If

    Exit_Comando1_Click:
    Exit Sub

    Err_Comando1_Click:
    MsgBox Err.Description
    Resume Exit_Comando1_Click
    End Sub

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    Thank you Hans,
    Now when I click the Galleria button, Sub Comando1_Click() runs without stopping but the following click gives the following message:
    <font color=448800>Unable to evaluate the expression for FieldSet 'Testo96'. Expression: 'IIf([ore_f2]=0 Or
    [testo116]=0,Null,([ener2]/[ore_f2])/([testo116]))'</font color=448800>
    I've tried deleting the Testo96 text box from frmPortate but I still get the same error message.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    But you can see that the chart shows three series so how can the FieldSets collection be empty?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    I assume that

    .Fields("Testo122").IncludedMembers = _
    Array("Federal Shipping", "Speedy Express")

    was just an arbitrary example to test if you could include members. In your form, it doesn't make sense - the contents of Testo122 are numeric. Try setting IncludedMembers to something meaningful.

    BTW, if you post another of your endless stream of attachments, could you please take out the reference to the Microsoft Office Web Components library before zipping the database?

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    <hr>BTW, if you post another of your endless stream of attachments, could you please take out the reference to the Microsoft Office Web Components library before zipping the database?<hr>
    Sorry Hans. I put the reference to OWC in because I wanted to check help for it and forgot to delete it. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Help for the IncludedMembers Property says, "You can set this property to Empty (IncludedMembers = Empty) or to a zero-length Variant array (IncludedMembers = Array()) to clear the included members list for the specified field."
    I've also read on a book about Access that if you submit an invalid list to the IncludedMembers Property then all the values are restored hence the code
    With frm1.PivotTable
    .ActiveView.DataAxis.FieldSets("Testo122") _
    .Fields("Testo122").IncludedMembers = _
    Array(1000, 1000)
    in the attached mdb.
    So now clicking the Galleria button doesn't cause any error but the relevant series on the chart isn't excluded when I click it.
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    There may be a misunderstanding here. The IncludedMembers and ExcludedMembers properties are used to include/exclude individual values, not series. Do you want the user to be able to hide/show an entire series?

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    Yes <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Including and excluding series in a Pivot Chart (2003)


  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including and excluding series in a Pivot Chart (2003)

    I've already read through it but I can't find a way to make it so that the user could choose whether to display or not series in a pivot chart view of a subform.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •