locked
FEATURE OVERHAUL REQUEST: Charts : "Microsoft Graph Chart" (in Forms and Reports) RRS feed

  • General discussion

  • Re: Charts in Forms & Reports

    This control - which is pretty much integral to Access - still looks like something out of Windows 3.1 that Microsoft rushed out of the door in order to be "first to market". What should be one of Access's strong points is still a real mess, almost 20 years after Access was first introduced. Try the following for example:

    • Create a chart. Try editing the TITLE (should be the simplest thing you can do with a chart). You cannot drag the dragging handles! You cannot resize the title! You can only MOVE the title within the chart area. SO what's the point of having dragging/resizing handles if you can't drag them or resize the object? Now try editing the title text, using the WYSIWIG editor! Just watch the text spill over the end of the title box - and watch how Microsoft's chart editor fails to scroll the WYSIWIG title editor in accordance with the position of the caret/ cursor. Very poor, and most inconvenient: you cannot see what you are editing, and half the time, your edits are not reflected in the text when you click back on the main chart area.
    • Can anyone explain to me how to change the data ranges on which an Access chart is based, AFTER creating the chart? I know how to change the SQL query the chart is based on (use Form/Report Control Property Sheet / Data tab); but how can I bind the chart itself to different data fields? The only way I have found is to delete the chart and start again using the "Wizard" (thus losing all the formatting work you've done on the chart, which is no small thing as you'll see below). I've searched high and low for the answer within the chart editor, and just can't find it.
    • Why are there so many different places I have to click/ menu items to search through, every time I want to change something on a Chart? CHART OBJECT: Edit/Open/Convert... Double click on the thing to open the chart view and actually change its design: Format Legend / Format Data Series / Chart Type... / Format Plot Area / Chart Options / etc.? What makes this even worse is that some of these options (as per the Excel 2010 chart editor, which is somewhat better) require you to click/ right-click on tiny areas of the chart/ borders/ lines, in order for the relevant options to be accessible. Other features (such as, changing the color/pattern of a particular Legend Key using the Format Legend Key dialogue) only appear to be accessible after a series of clicks (Open Chart Editor (wait for controls to finish jumping around on your screen) -> Click on Chart Area -> Click on Legend -> Click on a particular Legend Key [which is usually a tiny area to select]). If you mess up one of these steps in this long-winded process, or try doing it too quickly for the sluggish Chart control to respond; you can very easily accidentally change large parts of your chart/graph, and then it's impossible to "Undo" these changes.
    • Try editing some of the formatting on the chart. All sorts of weird things happen, like, you edit the formatting, save the Form/Report design, and then find that Microsoft Access has lost your edits: the Chart appears exactly as it did before the formatting amendments. Other strange things can happen: depending on the aspect ratio of the bounding box of the chart, pie charts can sometimes come out as ellipses rather than circles, and there's just NOTHING that you can do internally within the chart area, in order to fix this. The Chart control attempts to be a WYSIWIG editor but is nothing of the sort: often, you edit things in the Chart control editor, come out of that into the Form/ Report Design View, and find that the Chart now looks quite different to how you specified it in the editor.
    • Perhaps worst of all: the chart sometimes appears to get stuck, and despite refreshing data/ changing data that the chart's query is based on, Access has rendered the chart, and refuses to re-render it until something big happens like... restarting your computer and (if not that, at least) restarting Access. It behaves like a badly designed component from an early version of Excel, that Microsoft threw into Access just to be able to say they had charts in Access... Not like something that's a true part of MS Access.

    We really need something much better. Either this chart is garbage by design, or else it's so full of bugs that it should be deprecated and replaced. @Microsoft: Will you do something about this please? And does anyone have any suggestions about how to work around the limitations of the Microsoft Graph Chart in the period until Microsoft fixes this?


    Matthew Slyman M.A. (Camb.)

    • Changed type Matthew Slyman Friday, March 2, 2012 1:48 PM Is discussion not question
    Friday, March 2, 2012 1:39 PM

All replies

  • I agree that Access charts look very old-fashioned now.

    If I want a better-looking and easier-to-maintain chart, I prepare a query in Access, import the data into Excel (a database query in Excel can be refreshed), and create a chart based on the imported data.


    Regards, Hans Vogelaar

    Friday, March 2, 2012 3:25 PM
  • Over the last few years I have found it better to do most chart formatting in VBA.
    To that end I have tried to save the VBA in one demo and reuse it if need be.

    If it’s of any help to anyone the demo can be located on SkyDrive @:-

    https://skydrive.live.com/?cid=55AF3BDE0AE0E03A&id=55AF3BDE0AE0E03A!111#cid=55AF3BDE0AE0E03A&id=55AF3BDE0AE0E03A!144

    It may cause screen flicker in Access 2007 but I can’t fix it. If the screen does flicker try turning off 'Show Mouse on Plot Area'.

    The demo is in Access 2003 (2000 file format).

    Chris.


    Brisbane Australia, GMT+10, Access 2003

    Friday, March 2, 2012 6:11 PM
  • Nice piece of work Chris and thanks for sharing this! :)


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Friday, March 2, 2012 6:19 PM
  • Thanks, Daniel, I hope everything worked correctly with your version of Access.

    Cheers,
    Chris.

    Brisbane Australia, GMT+10, Access 2003


    Saturday, March 3, 2012 3:19 AM
  • Yup I tested all functionalities you had available in your demo in Access 2007 and it worked very well! :)

    Cheers,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Saturday, March 3, 2012 3:25 AM
  • And no flicker on mouse move over the plot area? I have had some complaints about that.

    Very interesting and thanks for testing,
    Chris.


    Brisbane Australia, GMT+10, Access 2003

    Saturday, March 3, 2012 3:31 AM
  • Yup, there is flickering, when loading the Chart and it takes a bit to load the Chart, but when it's loaded it runs smooth and I turned of "Show mouse on plot area".


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Saturday, March 3, 2012 4:04 AM
  • I haven't tested it yet but I am intrigued.

    Is this a set of VBA code for setting up & manipulating the standard charts & graphs? If so, I'm not surprised if there's flickering and even long pauses in rendering, with the user-interface sometimes appearing unresponsive for 1/2 second at a time - this is very typical for the Microsoft Graph Chart control. If this is what you've done, you might well have significantly improved the control for developers, but end-users will still find it very un-ergonomic, and rather unimpressive - though this is no reflection on your good work of course. One of the problems I'm having is when Access has supposedly already loaded data into a Report, and has supposedly already rendered the Graphs & Charts; and I scroll the Report... Scrolling is interrupted... Graphs & Charts appear to hold things up far more than the other Report controls. They really slow down the user-interface. From my casual perspective, they appear to be reloading data all over again every time you merely SCROLL the report they are based on! At least, they block the user-interface for long enough to do that, and I've seen some weird things happening when scrolling like the other day when I saw two vertical scroll bars on my report after the scrolling was interrupted by chart rendering.


    Matthew Slyman M.A. (Camb.)

    Saturday, March 3, 2012 8:25 AM
  • Matthew.

    >>I haven't tested it yet but I am intrigued.<<
    >>Is this a set of VBA code for setting up & manipulating the standard charts & graphs?<<

    I would never make that statement and then ask that question.
    If I was interested in something I would do the work to find out for myself.

    I have done the work in producing it and I do not believe I should have to do more work trying to explain what it is to someone who hasn’t test it.

    The file name is LineGraphSyntaxDemoA2003.mdb
    What that means is that it is a Line Graph Syntax Demo written in Access 2003 and it’s an MDB file which exposes the code so others may use it if they want.

    I do not want to get into very un-ergonomic slow reports which may or may not show two vertical scroll bars under your circumstances.

    It is what it is and I have done the work to produce it. Others can use it if they want, it is not mandatory.

    If I really wanted to complain about something I would say that it works without flicker in Access 2003 but it flickers in Access 2007. Under that circumstance, Access 2003 is better than Access 2007. Of course I could go out and buy Access 2007 just to fix the graph but I would be buying a product that is known to me to be worse than the product I already have. I am not going to do that because it does not make sense to me to deliberately buy a faulty product just to try and fix it.

    I do not need the shrink wrapped fancy stuff.
    If I want the fancy stuff I’ll write it.
    If I write it I’ll have the source code and should be able to fix the things I write.

    What I want is a stable platform on which to write code.

    Chris.

    Brisbane Australia, GMT+10, Access 2003

    Saturday, March 3, 2012 9:57 AM
  • I quite agree with a lot of what you say.  The chart features in SQL Server SSRS are pretty damn good and perhaps you should try them?  SSAS is quite cool too for pivot tables.

    Charts are reports which should be published around an organisation, so why keep them private in Access or Excel?  Plus both applications are vulnerable to people "tweaking" the data which is not done quite so easily in a secured database.

    I am sure Microsoft must have considered upgrading the charts functionality - and the pivot table functionality (which I would describe in similar terms to how you describe chart functionality.)    Sadly developemnt effort has gone in other directions like multiple tabs, more wizards etc.


    PG A bit of experimentation by trial and error often helps.

    Saturday, March 3, 2012 10:39 AM
  • For security reasons, I'm extremely minimalistic about the software I install, and the code I run. I just wanted a rough idea about what your code was about, before downloading and running it. With all due respect, although you seem like a nice guy, we don't know each other in the "real world". I think it's sensible to be a little paranoid as a software developer, to prevent my customers from getting problems...

    Matthew Slyman M.A. (Camb.)

    Monday, March 5, 2012 10:19 AM
  • I have never used Access Chart control. If a report should reflect more than just numbers, I use Excel.

    But it became interesting for me to see it. I put a Chart control on a form, look at 3 wizard steps (amazing pictures, BTW, smell like Win 3.1). Now, a Chart control appears on the form. Feel like I have a time machine... Double click, I'm in a chart editor and simultaneously I'm in 90s, sitting in front of Win95 of my friend, I had no own PC that times. How? How can this interface still be here? It's like to buy a Ferrari Enzo and find there a car cassette radio and no digital devices at all. After watching something like Component One's video of their visualization controls for Visual Studio, it's very difficult to not shed a tear.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru


    Monday, March 5, 2012 5:18 PM
  • I am quite surprised that the Chart Object used in Access isn't build on the same Object as Excel is, maybe for the reason that you are avoiding using the Chart Object in Access and will use the one in Excel, other than that it doesn't make sense. 

    I hope they will use the same Object in the future, I haven't seen the Access 2010 version yet, so I have no idea how it looks like there.


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Monday, March 5, 2012 10:50 PM
  • The chart control in Access 2010 is still the same as that in Access 95...

    Regards, Hans Vogelaar

    Monday, March 5, 2012 11:03 PM
  • Thanks Hans, that's quite dissapointing then ...


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

    Monday, March 5, 2012 11:06 PM
  • Hi Matthew Slyman and All,

    Thank you very much for your feedback and feature requests about the Access products in the forum. I have reported it to our internal portal.  Every feedback or request submitted will be evaluated carefully by our engineers.

    Thank you for your valuable feedback again!

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, March 21, 2012 4:36 AM
  • Hi Chris,

    I am trying to find the demo located on your skydrive but the file no longer appears to be there. Is there a way you could email me the file please?

    Thanks,

    Jeff

    Monday, June 18, 2012 11:35 PM
  • Hi Jeff.

    You should be able to find the file by the link in my signature below.
    (Should be at the top of the list.)

    Regards,
    Chris.

    Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive

    Wednesday, June 20, 2012 1:44 AM
  • Matthew, hear, hear.

    I could write my own thousand word rant on this.  The more I learn about charting in Access, the more dangerous it gets.  Tiny little tweaks tend to permantantly blow up my chart design.  No doubt I've contributed to the process, but Access punishes mistakes and experiments all out of proportion.  What's more, I KNOW it is full of bugs and trying to decipher a bug from a programming error is near impossible.

    A workaround?  Use Excel,  Ten times easier, more stable and intuitive.  Myself, I don't like working in Excel if I don't have to, so I've been doing things the hard way in Access, but I wouldn't send anyone else down that path without a clear warning of the difficulties.

    I don't even want to get started complaining about this, but here's my current bugaboo.  One set of values I am charting does not autoformat the Y axis.  All the others work fine, but this one always sets the minimum scale to zero, so I wrote some codes to adjust for it to set the minimum scale at one major gridmark below the minimum value.  It worked perfect and the graph is much more descriptive, but when I set the axis back to automatic for the other values I chart it never quite get back to normal.  So by fixing one chart, I have botched up 5 others.  That's just one.

    Don't get me started about the chart wizard.  Sometimes I think I'd rather bang my head against the table.


    TIA, Paul

    Thursday, July 19, 2012 6:54 PM
  • I like Hans's idea.  Do the complex number crunching in Access and port the results over to Excel, then do your charts and graphs in Excel.  Here is a way to get your Access date into Excel:

    http://translate.google.pl/translate?js=n&prev=_t&hl=pl&ie=UTF-8&layout=2&eotf=1&sl=pl&tl=en&u=http%3A%2F%2Fafin.net%2FKsiazkaSQLwExcelu%2FGraficznyEdytorZapytanSqlNaPrzykladzieMsQuery.htm

    Here are two great resources for charts & graphs:

    http://peltiertech.com/Excel/Charts/ChartIndex.html

    http://www.andypope.info/charts.htm


    Ryan Shuell


    • Edited by ryguy72 Friday, July 20, 2012 3:29 AM
    Friday, July 20, 2012 3:29 AM
  • Hi

    "Back to the future" 6 years ahead, could you make this code available again? I'm working with Access 2013 right now and just got those same chart problems, so I would like to try your solution, if possible.

    Thanks in advance.

    Thursday, June 7, 2018 12:39 PM