You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s guest blogger is Peter De Baets of Peter’s Software, providing Microsoft Access tools to developers for over a decade including ShrinkerStretcher, KeyedAccess and many other products and free downloads.
I've recently discovered an easy way to scale Access Chart objects at runtime to fit the form window, and I'd like to share it with you. Scaling an Access Chart means that when the end-user resizes the chart form window, the chart resizes to fit the new window size. It also means that when the Access window and form are maximized, the chart will fill the screen at any screen resolution. Well, it will mostly fill the screen, as we'll see below. Anyway, scaling an Access Chart is a no-brainer - it's a great way to add some WOW to your application with very little effort.
This tip is for forms that contain one and only one chart control (Unbound Object Frame containing a Microsoft Graph Chart object), and are intended simply to display that one chart.
I thought this would be fairly easy - you know, just set the chart control Height and Width to the form InsideHeight and InsideWidth property values in the form OnResize event procedure - but it turns out that there is a hitch. The chart object scales smoothly as the chart control increases in size until the width exceeds about 9.7 inches. Then the chart object undergoes an anomalous growth spurt and part of the chart object resizes beyond the edge of the chart control hiding some of the chart data! Come back, my chart!
The only way to handle this, as far as I know, is to limit the width of the chart control to a maximum of 9.7 inches so all of the chart data remains visible. Limiting the width of the chart control means there may be some empty form space on the right, so the code below also centers the chart control on the screen to make up for this.
Without further ado, here is the code to place in your chart form OnResize event procedure. Remember to insert the name of your chart control where indicated:
Dim wd As Long
With Me!OLEUnbound0 '<-- Your chart control name goes here
'* Make sure that the chart control size
'* mode is set to 'stretch'
.SizeMode = acOLESizeStretch
'* Make sure that chart fonts scale
'* with the chart object
.Object.ChartArea.AutoScaleFont = True
'* If window resizing causes horizontal
'* scroll bar to appear,
'* then move chart control out of the way prior to
'* setting left, width values.
If Me.Width > Me.InsideWidth Then
.Left = 0
.Width = 0
'* Set the new width of the chart control.
'* Chart object can resize beyond the chart
'* control when control is wider than ~9.7 inches, so
'* limit chart control width to 9.7 inches max.
wd = Me.InsideWidth
If wd > 9.7 * 1440 Then
wd = 9.7 * 1440
'* Set chart control width and height
.Width = wd
.Height = Me.InsideHeight - .Top
'* Center the chart horizontally on the form
If Me.InsideWidth > .Width Then
.Left = (Me.InsideWidth - .Width) / 2
'* Set the form width and height
Me.Width = .Left + .Width
Me.Section(acDetail).Height = Me.InsideHeight
Thats a nice tip group.In the same way, I would like to know, that is there any option to color differently for a single bar (based on my own criteria) in this bar graph ?
I have just experimented using simply the anchoring facility in Access. It seems to work until the form gets smaller than the original size of the chart object, which is then cropped. I tried it in both a tabbed form, and a modal/popup form. Anchoring for the chart control was set Stretch down and accross.
Srikanth, You can use: me!MyGraphControl.SeriesCollection(1).Interior.Color = vbRed ... to change the first series color to Red. Hope this helps, Peter De Baets
Roger, Thanks for pointing out that you can get a similar result with Access 2007/2010 control anchors. I've done some testing as well by setting the Horizontal and Vertical control anchors to "Both". Here's what I noticed: - When using control anchors to scale the chart object, it scales smoothly beyond 9.7 inches, and the chart object always stays within the chart control.
- As you noticed, when the form window is sized smaller than the chart control, the chart is cropped. A work-around would be to make the chart control fairly small so it can be scaled up.
- Scaling up with this method scales up the size of the grid lines (and all the lines) in the chart. This may or may not be desirable.
- These lines of code are helpful in the form OnOpen event procedure when scaling with the control anchor method. With Me!OLEUnbound0 '<-- Your chart control name goes here '* Make sure that the chart control size '* mode is set to 'stretch' .SizeMode = acOLESizeStretch '* Make sure that chart fonts scale '* with the chart object .Object.ChartArea.AutoScaleFont = True
End With - This method works with Access 2007 and later versions. All the best, Peter De Baets
Hi Peter. I agree that the method in your original article gives a more polished result. Thanks for that - I'll keep it by me for when the need arises. Roger.
It's worth noting that this code assumes .Left is zero initially. Also, if you have a header or footer, or if you display data above the chart, you may want to set the height as: .Height = Me.InsideHeight - .Top - Me.Section(acHeader).Height - Me.Section(acFooter).Height
Also, setting the form width and height at the end does not seem necessary, since you have already adjusted the control to match.
Steve, The form width and height are adjusted in case you are scaling down from a larger size. Without the adjustments, form scrollbars will appear. An alternative is to set the form ScrollBars property to "Neither", then you don't need to adjust the form width and height. Hope this helps, Peter De Baets
Thanks for the original code, and for responding to comments. After experimenting, I see now that the form dimensions still need the final adjustment, even though it seems like you have already accommodated the dimensions.