Create PieChart from unbound form?

[JeffBarker]

Hi guys, I've got a slight problem that I'm wondering if anyone will be able to help with.

I have a form that calculates the percentage of a product sold to customers.

The Main Form features two unbound text boxes you can select a date from and a date to, and an unbound combo box that looks up a list of products.

I then have a continuous SubForm that shows all the customers this product was sold to, how many units were sold to each customer and what percentage of the total amount their figure (sold) is.

The SubForm reads the criteria entered in the unbound date boxes and combo lookup on the Main Form using the following:

=[forms]![myform]![cboLookup]

and

=[forms]![myform]![txtDateField]

With a Search button on the Main Form that requeries the subform when you click it.

What I need to do is create a PieChart that shows the percentage of products sold per customer, but also updates when you click the Search button on the Main Form - so you can enter one set of criteria and view the results, then enter a new set of criteria etc and so on.

The PieChart wizard unfortunately only lets me use Tables or Queries as the DataSource for the PieChart so I'm thinking code would be the best way forward here.

I was toying with the idea of creating a new table to fuel the PieChart, which gets deleted and then replaced every time the Search button on the Main Form is clicked, but I'm struggling with the best way to do this - could anyone recommend a good slice of VBA to do the job, please?

Likewise, if anyone can think of a better alternative I'd be happy to consider that also.

Thanks in advance guys.

Jeff.

 

[JeffBarker]

By way of a bump, I've gone forward using the create table route - see my code here:

Private Sub cmdUpdatePieChart_Click()
    
    Dim obj As AccessObject
    Dim SQL As String
        
    SQL = "INSERT INTO tblMyTable ( Field1, Field2, Field3, Field4 )" & _
        "SELECT [Forms]![myForm]![cboMyCbo] AS Fld1, [Forms]![frmMyForm]![txtMyTextBox]/[Forms]![myForm]![mySubForm].[Form]![txtMyOtherTextBox] AS Fld2," & _
        "[Forms]![myForm]![mySubForm].[Form]![txtMyOtherOtherTextBox] AS Fld3, [Forms]![myForm]![mySubForm].[Form]![txtMyTextBox]/[Forms]![myForm]![mySubForm].[Form]![txtmyOtherTextBox] AS Fld4"
        
        
        For Each obj In Application.CurrentData.AllTables
            If obj.Name = "tblMyTable" Then
                If obj.IsLoaded Then
                    DoCmd.Close acTable, "tblMyTable", acSaveNo
                End If
                DoCmd.DeleteObject acTable, "tblMyTable"
            End If
        Next obj

    DoCmd.RunSQL "CREATE TABLE myTable ([Field1] text (20), [Field2] text (20), [Field3] integer, [Field4] integer)"
        
    Call FormatFieldToPercent1("tblMyTable", "Field3")
    Call FormatFieldToPercent2("tblMyTable", "Field4")
        
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings True

End Sub

But, what I'm stuck on is this:

Fields 1 and 3 are populated by cboMyCbo on the Main Form and txtMyOtherOtherTextBox from the Subform, and fields 2 and 4 are the percentages I spoke about earlier (one from the Main Form and one from the SubForm) - but they're all populating on the same record of the table.

So instead of my table looking like this:

Product Percentage
Field1 (cboMyCbo) Field 3 (myPercentage)
Field2 (txtMyOtherOtherTextBox) Field4 (myOtherPercentage)

It looks like this:

Field1 Field2 Field3 Field4

All on the same line. I can sort of understand that I'd need a recordset to carry out this operation, but I just need a gentle nudge in the right direction of how to do so.

As always, any help is appreciated.

*BUMP*

 

[JeffBarker]

Bit of a bump for the Thursday crew.