OWC11 w/MS ACCESS 2007

[Wyze]

After hours and hours and hours of searching the web user groups I have
almost gathered enough information for a working subroutine to create what I
need. I am down to the last couple of important pieces...

1) How to add multiple data columns to the "DropZones"
2) How to capture a user double click on a specific line chart "Marker" to
create a drill down effect.

Current code for creating the ChartSpace (That took way too long to write...)
And I do not really have anything useful for capturing the double click
event (that does not appear to exist on the object itself... **The
ChartSpace_MouseMove that people suggest isn't working for me**)

Please Note: I realize this is not pretty but I have not had time to make it
pretty as I am still trying to make it functional...

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Form_Open_Error

DoCmd.Maximize

Dim CS As Object
Dim strSQL As String
Dim strCON As String

Set CS = Me.CS1

strCON = _
"Driver={SQL Server};" & _
"Server=ServerName;" & _
"Database=DatabaseName;" & _
"Trusted_Connection=yes"

strSQL = "Select This, That, AndTheOther, PlusOneMore From ATable"

With CS
.Clear
.ConnectionString = strCON
.CommandText = strSQL
.Border.Color = RGB(153, 204, 255)
.interior.Color = RGB(153, 204, 255)
.DisplayToolbar = False
.DisplayPropertyToolbox = False
.DisplayFieldList = True
.DisplayFieldButtons = True
.AllowUISelection = True

'Need to add one more data column to this chDimFilter drop zone
.SetData CS.Constants.chDimFilter, _
CS.Constants.chDataBound, "This"

.SetData CS.Constants.chDimCategories, _
CS.Constants.chDataBound, "That"

.SetData CS.Constants.chDimValues, _
CS.Constants.chDataBound, "AndTheOther"

'Need to add one more data column to this chDimValues drop zone
.SetData CS.Constants.chDimValues, _
CS.Constants.chDataBound, "PlusOneMore"

'.SetData CS.Constants.chDimSeriesNames, _
CS.Constants.chDataBound, "AndTheOther"


.HasChartSpaceLegend = True
.HasSelectionMarks = chSelectionMarksAll
.ChartSpaceLegend.Position = CS.Constants.chLegendPositionTop
.Charts(0).Type = CS.Constants.chChartTypeSmoothLineMarkers

With .DropZones(chDropZoneFilter)
.ButtonBorder.Weight = CS.Constants.owcLineWeightMedium
.ButtonInterior.Color = vbWhite
.ButtonFont.Color = vbBlue
.ButtonFont.Size = 8
.WatermarkBorder.Color = vbWhite
.WatermarkFont.Color = vbBlue
.WatermarkInterior.SetSolid vbWhite
End With
With .DropZones(chDropZoneCategories)
.ButtonBorder.Weight = CS.Constants.owcLineWeightMedium
.ButtonInterior.Color = vbWhite
.ButtonFont.Color = vbBlue
.ButtonFont.Size = 8
.WatermarkBorder.Color = vbWhite
.WatermarkFont.Color = vbBlue
.WatermarkInterior.SetSolid vbWhite
End With
With .DropZones(chDropZoneSeries)
.ButtonBorder.Weight = CS.Constants.owcLineWeightMedium
.ButtonInterior.Color = vbWhite
.ButtonFont.Color = vbBlue
.ButtonFont.Size = 8
.WatermarkBorder.Color = vbWhite
.WatermarkFont.Color = vbBlue
.WatermarkInterior.SetSolid vbWhite
End With
With .DropZones(chDropZoneData)
.ButtonBorder.Weight = CS.Constants.owcLineWeightMedium
.ButtonInterior.Color = vbWhite
.ButtonFont.Color = vbBlue
.ButtonFont.Size = 8
.WatermarkBorder.Color = vbWhite
.WatermarkFont.Color = vbBlue
.WatermarkInterior.SetSolid vbWhite
End With

With .Charts(0)
.HasTitle = True
With .Title
.Caption = "Line Chart Activity"
.Font.Name = "Tahoma"
.Font.Size = 10
.Font.Bold = True
.Font.Color = RGB(0, 51, 153)
End With
End With
End With

Form_Open_Exit:
strSQL = ""
strCON = ""
Set CS = Nothing
Exit Sub

Form_Open_Error:
MsgBox Err.Description, vbCritical, "Error:"
Debug.Print Err.Description
Err.Clear
Stop
Resume Form_Open_Exit

End Sub