Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Charts based on changing criteria (Access 2000)

    All along I have been creating static queries for each of my Access Charts because the first time I did a criteria and asked the user for a start and end date and tried to pull a chart I got an error message. I have never been able to provide parameter pop ups with charts like I do with reports. Its funny, you get used to doing something, sometimes you forget to ask why. Why shouldnt I be able to build charts on the fly? After all, the query data isnt changing, just the parameters, right? Of course, if that original error was a fluke, I am going to feel really stupid after all this time.

    What I would like is some advice on is the best way to build graphs on the fly in Access. I dont want a solution- I want to figure that out- just guidance (unless someone has an example they would want to share). Can you build Access charts by receiving user input or changing criteria, like dates? Here is where I am conceptually:

    1) I could redefine static criteria in an existing query every time I wanted to create a different snapshot.
    2) I tried to implement a crosstab query, but it has its own intracacies. CTQueries chart to all the specifics, not the general catagories of a regular query that lists out items by line. Anyway, once I build the graph to a specifc, it is locked in and I cant just change it the way I can insert a new field into a query (to my basic knowledge).
    3) In Excel I can do Pivot Tables and then build graphs on the fly- Is there an Access version I can set up for a general user?
    4) Ultimately, I would love to generate graphs on the fly from a form, the same way I have a form that queries selected parts of a table on the fly.

    With reports, at least I can ask for some user defined input before generating the report. With charts, it feels like what you got is what you get. Are charts more or less static from the time they are built, or is there some advanced customization available? If I could do this, I could make it more user friendly, run several graphs one after the other from the same query, and long term could build a database that literally could run itself without any intervention from me.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Charts based on changing criteria (Access 2000)

    It is entirely possible to do what you want. The essential part is that you must requery the chart control when a parameter changes. I have attached a very simple (and not very useful) example.

    Also see ACC97: Microsoft Access 97 Sample Graphs Available in Download Center. The sample database illustrates a lot of techniques for manipulating charts in code; it applies to Access 2000 and later too.

  3. #3
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charts based on changing criteria (Access 2000)

    Thanks Hans. I have tried to do this on my own with some mixed results. One, when I load the chart, I get a default combo box selection and its appropriate value in the graph, but I dont get an updated graph with a new selection (no requery). Two, my SQL Query is a little different than yours and I am not sure why. I think the "Count" in the SQL may be doing this. I tried to get a shell down to 100k, but its impossible. One thing I did on creating the Chart was just put the category at the x-axis. I only used one value until I could figure out what was wrong.

    Here is my Combo Box:
    Name: Reason
    SELECT DISTINCT [QryManageReport].[ReasonSubCode] FROM QryManageReport;

    And my Chart:
    SELECT Null AS Expr1,Count ([ReasonSubCode]) AS [Count] FROM QryManageReport WHERE
    (((QryManageReport.ReasonSubCode)=[Forms]![ChtCountofReasons]![Reason]));

    The SQL Query for the Count item looks like this:
    Count: Count([ReasonSubCode])
    along with the regular Exp1:Null and ReasonSubCode with the control criteria.

    If you can give me one more hint, Ill go back to the drawing board.
    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Charts based on changing criteria (Access 2000)

    Although I would avoid Count as column name (since it is also the name of a built-in function), using it shouldn't prevent the chart from being updated. Have you created an After Update event procedure for the combo box? The one in my demo form looks like this:

    Private Sub cboCategory_AfterUpdate()
    Me.chtProducts.Requery
    End Sub

    This ensures that the chart reads its source data anew each time the user selects an item from the combo box. Without such an event procedure, the chart will not be updated.

  5. #5
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Charts based on changing criteria (Access 2000)

    It was that simple. I knew it was updating on open, but not with a new selection while it was open and the obvious never came to me. Ill clean it up and change the column name as well. Now I am going to work towards two combo boxes, and then date parameters. Thanks again Hans. Your patience and intelligence is greatly appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •