Solved

vba Chart Object X Axis is cut off when I make chart large

Posted on 2011-03-16
21
1,281 Views
Last Modified: 2012-05-11
Hi Experts,

I have a question that might be a bug.

I have a chart on a form. If the chart object is less than 25cm wide by 19 cm high, it displays data correctly. If I make the chart object > 25cm wide, the chart starts to cut off the data at the end of the x-axis.

Using the property for zoom, stretch and clip does not help.

WTF?

Thanks for your help.

0
Comment
Question by:WestCoastHip
  • 8
  • 8
  • 5
21 Comments
 
LVL 12

Expert Comment

by:telyni19
Comment Utility
Could you attach a sample database that exhibits this behavior? It would be helpful to be able to look at the form and chart properties to see what might be causing it.
0
 

Author Comment

by:WestCoastHip
Comment Utility
telyn,

Here is a description of the behavior. Unfortunately, this did not seem to remedy my chart.

http://blogs.office.com/b/microsoft-access/archive/2010/08/26/scale-access-chart-objects-at-runtime.aspx

It looks like an Access chart bug, and compounded with the tabbed control the chart is on, the provided solution does not seem to remedy.




0
 
LVL 12

Expert Comment

by:telyni19
Comment Utility
It definitely sounds like Access has some odd behavior as standard. What does the chart do when you use the code in the article you referenced? You said it didn't help, but does it do the same thing as without the code, or does it do something different but equally bad?
0
 

Author Comment

by:WestCoastHip
Comment Utility
telyn,

The same behavior occurs using the suggested fix at ->http://blogs.office.com/b/microsoft-access/archive/2010/08/26/scale-access-chart-objects-at-runtime.aspx

For now, just keeping it as is........and tying a magnifying glass with a piece of string to the monitor.

Caveman Style. Fixing stuff since.......cavemen.

Thanks.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
So, will you be posting a sample file?

This will save us all the time involved in "Guessing" what the issue might be...
Sound fair?

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the database window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. Post explicit steps to replicate the issue.
13. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue, and if applicable, also include a Graphical representation of the Exact results you are expecting.

JeffCoachman
0
 

Author Comment

by:WestCoastHip
Comment Utility
Jeff,

Sorry to not have provided the database. I would estimate the time involved to perform the steps is close to 5 hours, probably not going to happen.

Instead, here is a simple jpg of what the chart looks like when under 25 cm, and what it looks like extended to 31 cm.

You can see the data series cannot be contained by the chart object.....no matter how hard I try.

Thanks for looking.

goodlooking-25cm-chart.jpg
OOPS-the-chart-got-too-big-for-i.jpg
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 250 total points
Comment Utility
It does look good at 25 cm, but I can't see how small it is relative to your whole application. Why not just center it within the available space and leave it at 25 cm if that is the width that will show the chart the best without cutting off part of it?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
The issue here is that there are too many unknowns here:
How was the graph created?
What type of control is displaying the graph?
What is the size of the graph object inside the report.
In Office, what "Document Window" option are you using?
What are the size properties of the Report/control?

Again, perhaps this will end up being something simple, I just don't want to spend time "Guessing" at all the possibilities...

As I said, all I need is a "Sample",  with just made-up data.

In other words just post a database with enough data to display this issue.

I create standard charts with Access (using the chart Wizard) and I have never seen this issue.
So again, it could be something simple, it could be something complex, it may even be a combination of things...

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<I would estimate the time involved to perform the steps is close to 5 hours>
?
5 Hours?!... just to make a chart of this data?
DataMonthDay      DataValue
9/10/2011      3
10/10/2011      8
11/10/2011      6
12/10/2011      3
1/10/2012      2
2/10/2012      4
3/10/2012      17

I made a sample in 5 minutes, not 5 hours...

In any event here is my sample of your data in a chart.
I have no issues resizing the chart and having it display all the data.

JeffCoachman
Database410.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Chart in a form as well, again, no issues...
Database410.mdb
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:WestCoastHip
Comment Utility
Jeff,

Thanks for your input.

I realize that it must seem that it is not too big of a deal to send an mdb. The issue is that a quick look tells me that the code on the form to generate the 6 charts, each on a different tab is @ 4000 lines.

I posted a link from MS that states that this is a known issue.

What I am looking for is ideas other than the ones suggested on that link to remedy the issue.

The issue with leaving the charts at 25cm, is a matter of aesthetics. I can telll you that a 25 cm chart on a 60 cm monitor looks a little ridiculous though, especially on a full screen application.

On second thought, here is a sample of exactly what is happening. Take a look for yourself.

You are correct it took 5 minutes. I was thinking that I would send the existing app, but it the same for all charts.

Take a look at each tab. All are the exact same chart, just some tabs have a >30 cm width, others are 25cm. (the magic number is 9.2 inches)

Thanks


Database1.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Did you look at the charts in the sample you posted?

I looks like the Series is flipped... (In Rows when it should be in columns or vice versa)

In any event, (if this is what I think it is), this is more of a apparition, then a bug.
This is what happens when you insert the chart and do not resize it.

For some reason when you insert a chart in MS Access, the Preview is a bit "Off", (For lack of a more descriptive explanation)

So on the charts that are "Off", all you might have to do is resize the chart slightly.

Also note that this might just be a function of the chart being to big (Wide) for the screen.
In this case you would just have to scroll over.

In any case, here is your sample back
All charts display fine for me.
Let me know what you think.

JeffCoachman
Database1.accdb
0
 

Author Comment

by:WestCoastHip
Comment Utility
Jeff,

These charts do not display fine on my PC. If you notice that the charts on the tabs are different. I am not concerned with the data in any way.

I am concerned that the chart is not contained properly within the chart object. Did you see the different charts on each tab?

The first tab, the chart looks fine (image provided). Complete chart and legend. On the second tab(image provided), you can see that the chart only shows a part of the previous chart, and the legend is off the object somewhere. The x axis is cut off as well (no end tick visible)

This IS A BUG as stated at the MS Access blog (http://blogs.office.com/b/microsoft-access/archive/2010/08/26/scale-access-chart-objects-at-runtime.aspx). I am merely looking to see if anyone has had any luck with work arounds that do not include:
1. Make chart less than 25 cm
2. Buy a smaller monitor with less resolution.

The chart is also not too wide for my 55cm monitor. In fact, the reason I want the chart larger is to fill more of the monitor.

I also do not believe the chart in the sample I sent you "looked fine". Did you notice that those are the exact same charts on all tabs except for width, but some do not have legends? The legends are not turned off, they are outside of the chart object, and the series is being cutoff as well. Take another look. If you have screen shots of them looking fine on all tabs, please let me know if you have applied any patches to your Access (I am 2007(12.0.6535.5005) SP2 MSO(12.0.6545.504).

Thanks Tab 1 the chart is ok, full x axis, legend Tab 2, the chart has partial x axis, and no legend. where are they?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
Comment Utility
OK, I see what you mean now...
If there is a workaround for this, the author would be obliged to let everyone know.

Whats the reason why the charts have to be in a Form?

Unless I need "Interaction" I rarely put charts in Forms.

The charts will scale automatically if they are in a report, and opened explicitly in Print Preview.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
screenshots of Reports
untitled1.JPG
untitled2.JPG
untitled3.JPG
0
 

Author Comment

by:WestCoastHip
Comment Utility

The reasons the charts are in a form is because that is what the design states for the application. This has been spec'd by the client whom sends me money in order to implement designs he comes up with.

I am resigned to making the charts no wider than 9.2 inches and writing this off as a bug by MS, so if client wants bigger charts they can buy a decent 3rd party chart library.

I appreciate everyone's input on this.

Cheers


0
 

Author Closing Comment

by:WestCoastHip
Comment Utility
This is a MS Chart Object bug. It appears there are no additional workarounds other than the options found at http://blogs.office.com/b/microsoft-access/archive/2010/08/26/scale-access-chart-objects-at-runtime.aspx
0
 
LVL 12

Expert Comment

by:telyni19
Comment Utility
I don't think I should get all the points for this. Boag2000 obviously did a lot of work looking into the issue. I may have suggested the action you're actually going to take, but I'm not the only one who helped you come to that conclusion.
0
 
LVL 12

Expert Comment

by:telyni19
Comment Utility
Sorry, I didn't see it was shared, since there were so many comments and the selected one was short. I'm glad it was.
0
 

Author Comment

by:WestCoastHip
Comment Utility
I appreciated the comments from both Boag and yourself. The points were split evenly.

The solution I am using is exactly what you suggested.  

This question was like that old joke:

Old Guy: It hurts when I do this!
Doctor: Then stop doing that!

Thanks to both of you for your opinions.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Great.

...and thanks for taking the time to clarify the issue.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now