Programming Modern Charts with Colin Riddington

Colin Riddington delivers a masterclass on programming modern charts in Access, with a special focus on customizing charts at runtime.

In this comprehensive DevCon Vienna presentation, Colin shares his deep expertise on the latest capabilities of modern charts in Microsoft Access, particularly focusing on the significant updates released in 2023. He demonstrates how developers can programmatically customize charts at runtime, save those customizations, and create dynamic visualization experiences without having to recreate multiple chart forms.

Whether you're looking to implement sophisticated data visualizations or simply want to allow users to customize their charts without needing designer permissions, this detailed walkthrough offers valuable techniques that extend the power of Access's built-in charting capabilities.

Evolution of Modern Charts in Access

From Limited Beginnings to Expanded Capabilities

• Introduced in 2019 as an easier alternative to the notoriously difficult ActiveX charts
• Originally limited to just 5 chart types with minimal customization
• Significant update in Autumn 2024 (Access 365 only) added 11 new chart types
• Further improvements included grid lines, sorting capabilities, and chart-specific formatting options
• Full VBA support finally added in March 2025 (version 2502 or later)

Key New Chart Types

• Pareto chart (added after user request)
• Box and whisker charts
• Funnel charts
• Waterfall charts
• Word cloud (unique to Access among Office applications)
• Donut and half-pie charts

Programming Chart Objects

Chart Object Hierarchy

• The Chart object itself (54+ members)
• ChartAxes collection (for date grouping)
• ChartValues collection (for aggregation)
• ChartSeries collection (for individual series formatting)
• Format section with 27 different members

Runtime Chart Creation

• Programmatically create and configure charts from scratch
• Set chart types using either property names or enum values
• Configure borders, legends, and other visual elements
• Set up series with appropriate display names and formats

Customizing Chart Elements at Runtime

Chart Object Properties

• Change background style (transparent or normal)
• Modify border styles (type, color, width)
• Add and format titles and subtitles
• Position legends (top, bottom, left, right)
• Configure axis titles and formatting

Data Visualization Options

• Group dates by day, month, quarter, or year
• Change aggregation methods (sum, count, average, etc.)
• Apply sorting (ascending, descending, or none)
• Customize grid lines (major, minor, both, or none)

Series-Specific Customization

• Modify display names (appears in legends and trend lines)
• Change series colors
• Add data labels
• Configure trend lines
• Adjust line styles and weights
• Add markers with custom shapes and colors

Chart-Specific Features

Pie, Donut, and Half-Pie Charts

• Display data labels as values or percentages
• Position labels (inside, outside, best fit)
• Configure multiple rings for donut charts
• Limited ability to control slice colors (a significant limitation)

Line and Scatter Charts

• Control line styles (solid, dashed, etc.)
• Adjust line weights (using unique line weight units)
• Add markers with various shapes
• Configure missing data handling (plot as zero, don't plot, or interpolate)
• Add trend lines (linear, moving average, exponential)

Other Specialized Charts

• Box-whisker charts for statistical display
• Bubble charts with size representing values
• Funnel charts with sorting options
• Word cloud with layout configuration
• Super-wide charts that span multiple monitors

Saving Runtime Customizations

Technical Implementation

• Store chart settings in a table when saved
• Retrieve and apply settings when form reopens
• Use dictionaries to track changes between series
• Apply saved settings without modifying the original design

Benefits of This Approach

• Single chart can be reused for multiple visualization purposes
• Users can customize views without needing design permission
• Maintains performance by avoiding unnecessary form recreation
• Changes persist between sessions when saved

Limitations and Future Improvements

Current Limitations

• Cannot individually color data points
• Cannot mark or highlight individual points
• Limited control over data label formatting
• Cannot change orientation for word clouds (it appears briefly then resets)
• Chart colors in pie charts cannot be specifically assigned

Anticipated Improvements

• Individual data point formatting
• Better orientation controls for word clouds
• Enhanced data labeling capabilities
• More control over colors and gradient fills

Conclusion

Colin Riddington's presentation demonstrates that Access modern charts have evolved significantly from their limited beginnings. The combination of VBA support and runtime customization capabilities now makes it possible to create sophisticated, user-configurable data visualizations directly within Access. While limitations remain, developers no longer need to resort to exporting to Excel for most common charting scenarios.

Access to Recordings

This presentation was part of Access DevCon Vienna, a paid online conference for Microsoft Access developers. The full recording and other conference materials are exclusively available to registered attendees of the conference.

If you attended Access DevCon Vienna, you should have received an email from the conference organizers with a link to the videos.

Consider Attending Future Conferences

The quality and depth of content from Access DevCon Vienna demonstrates the value of professional development conferences focused on Microsoft Access. Consider attending future conferences to:

• Learn advanced techniques directly from expert presenters
• Gain access to exclusive content not available elsewhere
• Ask questions of expert presenters and other attendees
• Stay current with the latest Access development trends and best practices

For information about upcoming Access DevCon Vienna conferences, visit donkarl.com.

Acknowledgements

• Initial draft generated by Claude-3.7-Sonnet