Set a pie chart with values from a listbox access vba

[b24 sofwtares]

hi, i have listbox with some columns. on a form, i have insert a pie chat. i wan that when i select a row in the listbox, it should display me the pie chat with values from the columns 5 and 6 of the row selected. here is the complete code
Private Sub Years_listbox_Click()
If Me.Years_listbox.ListIndex <> -1 Then
Me.Modify_year_btn.Visible = True
Me.Chart50.Visible = True

Me.Chart50.ChartTitle = "Revenues vs Expenses " & Me.Years_listbox.Column(0, Me.Years_listbox.ListIndex)
Dim selectedItem As Variant
selectedItem = Me.Years_listbox.Column(4, Me.Years_listbox.ListIndex) ' column 5
Dim revenues As String
revenues = selectedItem
revenues = CDbl(Replace(revenues, ",", ""))

selectedItem = Me.Years_listbox.Column(5, Me.Years_listbox.ListIndex) ' column 6
Dim expenses As String
expenses = selectedItem
expenses = CDbl(Replace(expenses, ",", ""))

'THE CODE TO DISPLAY THE PIE CHART SHOULD GO HERE

Me.Chart50.Visible = True

Else
If Me.Years_listbox.ListIndex = -1 Then
Me.Modify_year_btn.Visible = False
End If: End If
End Sub

 

[arnelgp]

create a new table and named it ChartData with fields:

ID (autonumber)
Entity (short string)
Value (Decimal, with Scale = 2)

now, add 2 records to this table:

ID--------Entity----------Value
1---------Revenues------0
2---------Expenses-------0

now change your code to:

Private Sub Years_listbox_Click()
    Dim db As DAO.Database
    If Me.Years_listbox.ListIndex <> -1 Then
        Set db = CurrentDb
        Me.Modify_year_btn.Visible = True
      
        Me.Chart50.ChartTitle = "Revenues vs Expenses " & Me.Years_listbox.Column(0, Me.Years_listbox.ListIndex)
        Dim selectedItem As Variant
        selectedItem = Me.Years_listbox.Column(4, Me.Years_listbox.ListIndex) ' column 5
        Dim revenues As String
        revenues = selectedItem
        revenues = CDbl(Replace(revenues, ",", ""))
        db.Execute "update ChartData set [Value] = " & revenues & " Where Entity='Revenues';"
        selectedItem = Me.Years_listbox.Column(5, Me.Years_listbox.ListIndex) ' column 6
        Dim expenses As String
        expenses = selectedItem
        expenses = CDbl(Replace(expenses, ",", ""))
        db.Execute "update ChartData set [Value] = " & expenses & " Where Entity='Expenses';"
      
        Me.Chart50.Requery
        'THE CODE TO DISPLAY THE PIE CHART SHOULD GO HERE
      
        Me.Chart50.Visible = True
  
    Else
        If Me.Years_listbox.ListIndex = -1 Then
            Me.Modify_year_btn.Visible = False
        End If
    End If
End Sub

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=90102

 

[b24 sofwtares]

create a new table and named it ChartData with fields:

ID (autonumber)
Entity (short string)
Value (Decimal, with Scale = 2)

now, add 2 records to this table:

ID--------Entity----------Value
1---------Revenues------0
2---------Expenses-------0

now change your code to:

Private Sub Years_listbox_Click()
    Dim db As DAO.Database
    If Me.Years_listbox.ListIndex <> -1 Then
        Set db = CurrentDb
        Me.Modify_year_btn.Visible = True
     
        Me.Chart50.ChartTitle = "Revenues vs Expenses " & Me.Years_listbox.Column(0, Me.Years_listbox.ListIndex)
        Dim selectedItem As Variant
        selectedItem = Me.Years_listbox.Column(4, Me.Years_listbox.ListIndex) ' column 5
        Dim revenues As String
        revenues = selectedItem
        revenues = CDbl(Replace(revenues, ",", ""))
        db.Execute "update ChartData set [Value] = " & revenues & " Where Entity='Revenues';"
        selectedItem = Me.Years_listbox.Column(5, Me.Years_listbox.ListIndex) ' column 6
        Dim expenses As String
        expenses = selectedItem
        expenses = CDbl(Replace(expenses, ",", ""))
        db.Execute "update ChartData set [Value] = " & expenses & " Where Entity='Expenses';"
     
        Me.Chart50.Requery
        'THE CODE TO DISPLAY THE PIE CHART SHOULD GO HERE
     
        Me.Chart50.Visible = True
 
    Else
        If Me.Years_listbox.ListIndex = -1 Then
            Me.Modify_year_btn.Visible = False
        End If
    End If
End Sub

View attachment 115852

i am so happy. thank you. but i have another preocupation, how do i format the chat, like i wan the datalegend to display as percentage, may be also change the colors for expenses and revenues... Thanks