PivotChart View - Events (1 Viewer)

[TimW]

Hi All,
I have created a chart using the form PivotChart view. I have done this initially using VBA, but i have also tried just using a query and puting two text boxes in the form.
The data is a summary of some other data in the database. (Number of faults, between 2 dates, for any selected production line, and product on any particular shift)
What I would like to do is click on one of the bars of the chart and run some VBA (For instance. Click on the highest bar (needle blocked, say) that would take me to a breakdown of occurances of needle blocked that occured in the original query. ( For instance, so I could see if a particular shift had that problem)
If I click on the chart I get it highlighted or on hover i get the value of the bar + the description
I have tried to name the chart in VBA

myForm.ChartSpace.Charts(0).Name = "Fred"

But i cant get an event to fire is I put this event on the form

Private Sub Fred_MouseDown(button, shift, X, Y)
etc...

I believe that Access knows which bar the mouse is over, because of the on hover letting me know....
I would like to use this to dynamically display a new graph based on the bar I have just clicked. (along with the original information on the associated form)
BTW - I have got around this by displaying a table next to the graph and the user can click on the table to get to the new information. However, this duplicates the data on the form and does not look good!

If anyone can help I would be very greatful.

Cheers

Tim W

 

[TimW]

Hi All,
This is my current solution for the Pivot chart click to drill down information. I have used the Before Screen Tip event and the selection change event. Not very elegent but there you go!
It requires the user to hover to obtain as screen tip before clicking the data point that they are interested in. One more step than i wanted but I am still interested in anyone can solve this for me. The code...

[FONT=Calibri][SIZE=3]Option Compare Database[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]Dim txt As String[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]Private Sub Form_BeforeScreenTip(ByVal ScreenTipText As Object, ByVal SourceObject As Object)[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]  Dim i As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  Dim str As String[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]  txt = vbNullString[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   If TypeName(SourceObject) = "ChPoint" Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       ' assign screen tip text to variable if it is a chart data point[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       txt = ScreenTipText [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   End If[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]
 
 
[FONT=Calibri][SIZE=3]Private Sub Form_SelectionChange()[/SIZE][/FONT]
 
[SIZE=3][FONT=Calibri]    Dim i As Integer[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Dim strAnswer(1 To 2) As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    Dim tempStr As String[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   ' only display message if valid screentip[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If Not txt = vbNullString Then[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]       ' find reject type part of txt[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       tempStr = Mid(txt, 8, InStr(8, txt, " - ", vbTextCompare) - 8)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       strAnswer(1) = tempStr[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       ' find date part of txt[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       tempStr = Mid(txt, InStr(8, txt, " - ", vbTextCompare) + 2, 8)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       strAnswer(2) = tempStr[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  '     Debug.Print strAnswer(1), strAnswer(2)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       MsgBox "Search for the following..." _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           & vbCrLf & "Reject: " & strAnswer(1) _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           & vbCrLf & "On: " & strAnswer(2), vbOKOnly + vbInformation, "Drill down results"[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   txt = vbNullString[/FONT][/SIZE]
 
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

The string manipulation is getting the information from the screen tip. There must be away to get this without this carry on! Suggestions please
The output from this strAnswer is then to be used in VBA and SQL along with the variables that I already have. (That created the graph in the first place!)
The message box will probably be changed to give an output
I will then use this info to create another graph based on the data point selected in the first one to drill down into the data.

 

[Alephu5]

I've been trying to do this all day, and have now got a working solution that doesn't rely on screentips. The following code goes in the form/subform that contains the pivotchart. If you're using a subform, ensure that you set the default view to pivotchart.

Private Sub Form_Click()
 
Dim objSelection As Object
 
Set objSelection = ChartSpace.Selection
 
  Select Case ChartSpace.SelectionType
 
    Case chSelectionCategoryLabel
      Msgbox objSelection.Caption
    Case chSelectionPoint
      MsgBox "The value is: " & objSelection.GetValue(chDimValues) & " and the category is: " & _
              objSelection.GetValue(chDimCategories)
  End Select
 
Set objSelection = Nothing
End Sub

It was a bit of a nightmare trying to figure out the methods/properties of 'Chartspace.Selection', but I managed to find them in the link at the bottom of the post (I haven't got enough posts for hyperlinks). I have done a minimal amount of testing, but I am pretty sure that you can replace chDimValues/chDimCategories in the snippet above with any constant in the class 'ChartDimensionsEnum' in the OWC11 library, provided it is relevant to your chart.

blogs.office.com/b/microsoft-access/archive/2008/01/13/interactive-chart-form-filtering-code-sample.aspx

 

[ericjfrench]

This did it for me...thanks to Alephu5, took me forever to find your post, but it got me close enough!

My plan is to take that value and add it to the where clause of an existing query and display the records in a report; a drill down into the data behind the graph...this will be slick!

Thanks again!!

Private Sub Form_Click()
Dim objSelection As Object
Dim strFilter As String
Dim i As Integer

Set objSelection = Me.ChartSpace.Selection

Select Case ChartSpace.SelectionType

Case 8

strFilter = objSelection.GetValue(0)
MsgBox strFilter
End Select

Set objSelection = Nothing