How Do I make my graph (Bar chart) dynamic 1

[jabmod]

I have a gragh on a form that shows data for each month of the year so the grapgh has 12 bars.

I would like to be able to click on each bar and have it open up a report or table that shows the detail record for that month.

Thank you very much.

 

[FancyPrairie]

I don't have time right now to test this out. But it would be real simple for you to try. Simply place a command button over each of the bars and set the background property of each command bar to Transparent. Then use the onclick event of each command button to open the report you want.

 

[TheAceMan1]

Howdy FancyPrairie . . .

Great Idea! However a command button doesn't a background property! But a textbox does. Using the [blue]On Click[/blue] event of the textboxes should produce the same results . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[FancyPrairie]

You're right AceMan1, but I know I did something like that in the past. So I checked it out and it's the Transparent property that needs to be set to true. But, the textbox will also work.

 

[jabmod]

Thanks so much for your ideas.

In this case, the months will always be aligned in order each time. That is the Bars will always be in this order - Jan Feb Mar...Dec

Next Question: What if the bars are not always in the same order. For example if you have a graph with bars for Producs in order of # of sales. In this case, the product order (bars) will change based on the number of sales for each product.

How would you do this so that as the click on the product bar, the details for each product could pop up as a report?

Thank you.

 

[FancyPrairie]

The command bars don't need to represent a month. That is, the 1st command bar does not need to represent January. Rather, the 1st command bar represents the 1st bar. Your query will tell you which product is 1st, 2nd, and so on.

 

[jabmod]

I see where you are headed. However, my question from that is how do I refer to the particular bar on the graph withouth hard coding it to for example actual name of product or month name "January"?

Thanks.

 

[FancyPrairie]

What does your query look like?

 

[jabmod]

FancyPrairie -

My graph is based on a query that basically groups sales volumn by month and another graph with a query that groups sales volume for entire year by product.

The product graph could pull just the top 10 products for the year at any given time.
For example, these 10 product bars would change from time to time as products fall into or off the top 10.

Thanks

 

[jabmod]

FancyPrairie & TheAceMan1

Hope you have not lost me. I still need some direction with this.

FancyPrairie wrote:

"...the 1st command bar does not need to represent January. Rather, the 1st command bar represents the 1st bar. Your query will tell you which product is 1st, 2nd, and so on"

and my question was how do I refer to the particular bar on the graph without hard coding the month name e.g. "January"?

My query, which the graph is based on, is grouped by month name. Another group will have its query grouped by product name and this graph will only show top 10 products so the products on the graph will change from time to time.

Thank you, thank you

 

[SkipVought]

jabmod,

I notice that over the past five years, you have posted 41 threads and have received many good tips related to your stated needs. Yet, you have responded NOT ONCE, to
[blue]
Thank Tek-Tip Contributor
for this valuable post![/blue].

The little purple Stars accomplish several important things.

First, it gives positive feedback to contributors, that their posts have been helpful.

Second, it identifies threads as containing helpful posts, so that other members can benefit.

And third, it identifies the original poster (that's YOU, BTW), as a grateful member, that not only receives, but is willing to give tokens of thanks.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[FancyPrairie]

Assume your graph is based on a query. The query returns the top 10 products. When the user clicks on the 1st command button, you simply open the query to determine the name of the product for the 1st record.

For example, suppose your query looks like this:
Select ProductName, ProductValue from ProductTable Order By ProductValue;

Set the onclick event of all of your command buttons to a function (i.e. ProcessCommandButton). Assume your command button names are "cmd1", "cmd2", ... "cmd10".

Function ProcessCommandButton()
    Dim ctl as integer
    Dim rst as New ADODB.Recordset

    ctl = Mid(Me.ActiveControl.ControlName,4)  'Determine which record to read based on which command button was selected

    rst.Open "YourQuery", CurrentProject.Connection, adOpenDynamic, adLockReadOnly

    rst.Move ctl     'Move to record

    strProductName = rst.Fields("ProductName")

End Function