Using Treeviews with Access with Pete Poppe (Maj P)

Military precision meets database development in this comprehensive guide to implementing TreeView controls in Microsoft Access applications.

In this Access Europe User Group presentation, Pete Poppe (known as MajP in various Access forums) shares his expertise on working with TreeView controls in Microsoft Access. Drawing from his background as a Marine Corps officer and subsequent work in systems testing and evaluation, Pete demonstrates both the technical implementation and practical applications of TreeViews, with a particular focus on making them more stable and easier to use through his custom wrapper class.

Whether you're new to TreeViews or looking to enhance your existing implementations, this presentation offers valuable insights into handling hierarchical data structures and creating more intuitive user interfaces in Access applications.

Background and Experience

• Career US Marine Corps officer specializing in combat systems development
• Advanced degree in Operations Research
• Extensive experience with operational testing and data collection
• Self-described "power user" rather than professional developer
• Active participant in Access technical forums

TreeView Fundamentals

Core Concepts

• Displays hierarchical data relationships visually
• Two main types of TreeView controls available:
  • Microsoft ActiveX Control (Common Controls 6.0)
  • JKP Applications Development Services version
• Each node requires a unique key and display text
• Supports both hierarchical and organized data structures

Key Capabilities

• Expand/collapse functionality for nested data
• Drag-and-drop support (ActiveX version)
• Custom icons and formatting options
• Light loading for better performance with large datasets

Technical Implementation

Data Structure Requirements

• Self-referencing tables for hierarchical data
• Parent-child relationships clearly defined
• Null parent IDs for root nodes
• Enforced referential integrity recommended

Query Design

• Specific naming conventions required
• Concatenated identifiers for unique keys
• Careful handling of parent-child relationships
• Support for both hierarchical and organized data

Custom Wrapper Class

Key Features

• Simplified TreeView initialization
• Automated loading of hierarchical data
• Support for "light loading" of large datasets
• Enhanced event handling
• Drag-and-drop functionality

Benefits

• Reduces code complexity
• Improves stability
• Easier maintenance
• More consistent behavior

Practical Tips

Performance Optimization

• Light loading for datasets over 2,000 nodes
• Careful management of node creation
• Strategic use of expand/collapse
• Efficient query design

Common Pitfalls

• Sensitivity to key formatting
• Version compatibility issues
• Design-time stability concerns
• Memory management with large datasets

Conclusion

Pete's presentation demonstrates that TreeViews, while not native to Access, can be powerful tools for displaying and managing hierarchical data when implemented correctly. His wrapper class addresses many common challenges and makes TreeViews more accessible to Access developers of all skill levels.

Recording

The full recording is available on YouTube:

Join Live!

Want to get even more out of these presentations? Join the live Access User Group events! The next upcoming events are listed on the AUG Event Calendar.

Attending live gives you the opportunity to:

• Interact directly with presenters during Q&A sessions
• Network with other Access developers
• Share your own experiences and challenges
• Get immediate answers to your specific questions
• Participate in group discussions

With multiple user groups across different time zones (and languages!), you're sure to find a meeting time that works for your schedule.

Acknowledgements

• Base cover image generated by FLUX-schnell
• Initial draft generated by Claude-3.5-Sonnet