Adding Alpha Data Labels to an XY Scatter Plot

[kb_j]

Hey guys,

I'm trying to figure out a way to add data labels to an xy scatter chart. My data looks like this:

ID | X Value | Y Value
Job1 | 35 | 50


Job2 | 40 | 90
etc.

I'd like the chart to plot a single point for each record using the x and y coordinates, then add the value in the "ID" field as a data label. Any ideas on how to accomplish this in Access?

Thanks!

[June7]

Does this help http://www.utteraccess.com/forum/Bas...-t2003699.html

[kb_j]

Thanks, June! That's exactly what I need. Unfortunately, when it comes to VBA I'm a total noob. I've been trying to figure out how to take this code and tweak it to fit my needs for the last several hours - but to no avail. Could you help me determine what fields I need to change here? (And where to put it afterwards? Like I said - I'm starting at level 0.) Thanks!

Let's say:

Report Name = FeverChartReport
Graph Name = FCR
Label = JobID
X Values = ValueX
Y Values = ValueY

Dim i As Integer, cnnMe As New ADODB.Connection, rstMe As New ADODB.Recordset

    Set cnnMe = CurrentProject.Connection

    rstMe.Open "qryAll", cnnMe, adOpenStatic, adLockReadOnly
    rstMe.MoveFirst

    With Me.grpMain.Object.SeriesCollection(1)
        For i = 1 To .Points.Count
            With .Points(i)
                .ApplyDataLabels

                With .DataLabel
                    .Text = rstMe("strEngineer").Value & "; " & rstMe("X").Value & "; " & rstMe("Y").Value

                    With .Font
                        .Name = "Arial"
                        .Size = 10
                        .Bold = False
                        .Color = vbBlue
                    End With
                End With
            End With

            rstMe.MoveNext
        Next i
    End With

    rstMe.Close
    cnnMe.Close

    Set rstMe = Nothing
    Set cnnMe = Nothing

[June7]

I don't know your db so have no idea what names you should use. However, I suppose you need to look at using your equivalents for:

qryAll - maybe the same query used as the graph RowSource
strEngineer - maybe the ID field
X
Y
grpMain - the graph name

Put code in report Detail section OnFormat event. Code will only run when report is opened in Preview or direct to printer.

[kb_j]

Thanks, June. I made those changes you suggested. Now I get the following error (see pic) when I try to open the report in Print Preview.



Any ideas? It seems like I'm closer than before, just blocked by my own lack of experience with VBA. The fix is probably something simple that you folks with more knowledge would be able to spot quickly.

SingleStepProcessDB - Version4.zip

I'm also attaching a zipped copy of the database in case that helps. Basically, the graph (Graph1) on the report (FCR) is reading the data from the query LevelThreeCalcQ. The data for the labels is in the field "JobID" under the same query.

And here's the code with those edits you suggested, June:

Dim i As Integer, cnnMe As New ADODB.Connection, rstMe As New ADODB.Recordset


    Set cnnMe = CurrentProject.Connection


    rstMe.Open "LevelThreeCalcQ", cnnMe, adOpenStatic, adLockReadOnly
    rstMe.MoveFirst


    With Me.Graph1.Object.SeriesCollection(1)
        For i = 1 To .Points.Count
            With .Points(i)
                .ApplyDataLabels


                With .DataLabel
                    .Text = rstMe("JobID").Value & "; " & rstMe("PercentofProcessComplete").Value & "; " & rstMe("PercentofBufferConsumed").Value


                    With .Font
                        .Name = "Arial"
                        .Size = 10
                        .Bold = False
                        .Color = vbBlue
                    End With
                End With
            End With


            rstMe.MoveNext
        Next i
    End With


    rstMe.Close
    cnnMe.Close


    Set rstMe = Nothing
    Set cnnMe = Nothing

Boy, if I could just get a snippet of code that I could just drop in the right place I'd sure appreciate it!

Thanks for your help during my VBA "baptism by fire."

[June7]

I can't vouch for the code example I found. I did not look at in depth.

Here is tutorial on working with recordsets http://allenbrowne.com/ser-29.html
The examples are for DAO recordset.

Here is example of how I work with ADO recordset:

Dim rsPCIAGE As ADODB.Recordset
Set rsPCIAGE = New ADODB.Recordset
rsPCIAGE.Open "SELECT * FROM AAllAirportsAlbers ORDER BY ID;", CurrentProject.Connection, adOpenStatic, adLockPessimistic