Outside-the-Box Access UI Design with Pete Poppe (MajP)

Ever felt trapped by Access's native forms and controls when trying to build the interface your users actually want?

Pete Poppe (MajP) from Access World Forums shared his arsenal of creative workarounds for building non-traditional user interfaces while keeping your data properly normalized. The presentation covered innovative techniques for creating scheduling grids, checklist interfaces, cascading combo alternatives, hierarchical displays, and even map-based data entry—all without sacrificing database design principles. Pete demonstrated how to fake spreadsheet-like interfaces, calendar views, and tree structures using combinations of temporary tables, crosstab queries, unbound forms, and ADO recordsets.

Whether you're tired of fighting with subforms for complex data entry or looking for ways to give users the intuitive interfaces they crave without corrupting your data structure, this session offers a treasure trove of proven techniques and outside-the-box thinking.

The Core Problem

Why Developers Break Normalization

• Users want spreadsheet-like interfaces that don't map to normalized tables
• Complex data relationships are hard to visualize with standard forms/subforms
• Developers create non-normalized structures just to support perceived UI needs
• Even experienced developers fall into this trap with hierarchical data

Common Problem Areas

• Scheduling and calendar interfaces (like Outlook)
• Any grid-based data entry that feels "natural" as rows and columns
• Checklist selections from large lists of options
• Hierarchical or self-referencing data display
• Survey systems with complex many-to-many relationships

Key Techniques Demonstrated

Pseudo Grid Control (Scheduling)

• Problem: Conference room booking system across multiple buildings and time slots
• Bad Solution: 26 tables (one per room) with 18 fields (one per time slot)
• Good Solution:
  • Store appointments in normalized table (person, room, start/stop times)
  • Create display table with pre-built grid structure
  • Populate grid cells by reading normalized data
  • Use single continuous form bound to grid table
  • Tab controls switch which rooms display in columns

Preloading Records Strategy

• Scenario: Insurance quotes requiring exactly 5 companies per customer
• Alternative to Crosstab: Automatically create child records when adding new customer
• Benefits: Users see all required fields immediately without manual record creation

Multiple Checklist Approaches

• Data Structure: Always many-to-many with junction table
• Interface Options:
  • Subform with selection checkboxes
  • Multi-select list box
  • ADO in-memory recordsets for popup interfaces
  • Crosstab query display with click-to-edit

ADO In-Memory Recordsets

• Advantages: No table bloat, dynamic field creation, bindable to forms
• Use Cases: Building temporary grids without physical tables
• Implementation: Define fields on-the-fly, populate from normalized data, bind to form

Advanced Solutions

Cascading Combo Dilemma

• Problem: Users add unnecessary foreign keys to enable cascading in continuous forms
• Solution: Use popup forms instead of cascading combos
• Benefits:
  • Maintains proper data structure
  • Provides better user experience with filtering and sorting
  • Avoids circular relationships

Tree Views and Hierarchical Data

• Challenge: Self-referencing tables are simple but hard to display/navigate
• Solutions:
  • Native tree view controls for intuitive hierarchical display
  • Fake tree displays in forms/reports using saved sort order and indent levels
  • Recursive loading techniques for dynamic level structures

Pseudo Geo-Reference Forms

• Application: Locating items on maps or floor plans
• Data Structure: Simple X/Y coordinates with foreign key to map image
• Implementation:
  • Drag-and-drop class module for movable labels
  • Hidden labels revealed and positioned based on stored coordinates
  • Custom events report position changes back to database

Calendar and Unbound Form Techniques

When to Use Unbound Forms

• Good Cases: Fixed grid layouts, calendar displays, temporary interfaces
• Poor Cases: Standard data entry (better tools exist for that)
• Hybrid Approach: Unbound display with popup bound forms for editing

Calendar Implementation

• Structure: Grid of cells representing days/time slots
• Population: Read normalized appointment data into display cells
• Interaction: Click cells to popup standard appointment entry forms

Technical Considerations

Pseudo Grid vs. Pure Unbound

• Pseudo Grid Benefits:
  • Fewer controls to manage (6 vs. 108 in scheduling example)
  • Easy to modify time slots by adding table rows
  • Leverages continuous form features
• Pure Unbound: May work for small, fixed layouts

Performance and Maintenance

• Temp Table Strategy: Consider bloat with frequent add/delete operations
• Alternatives: Side databases, in-memory recordsets, compact/repair schedules
• Code Organization: Build reusable class modules where possible

Advanced Data Display

Rich Text Formatting

• Calendar Application: Multiple colored entries in single text box
• Implementation: Concatenate data with RTF formatting codes
• Benefits: Complex visual displays without multiple controls

Visual Hierarchies Without Tree Controls

• Technique: Save sort order and indent levels from tree view
• Applications: Reports, standard forms that mimic tree appearance
• Advantages: Printable hierarchical reports, unlimited dynamic levels

Conclusion

Pete's presentation demonstrated that creative interface design doesn't require abandoning proper database normalization. By combining temporary display tables, unbound forms, ADO recordsets, and strategic use of popup editors, developers can deliver the intuitive interfaces users want while maintaining robust data structures. The key principle throughout all techniques: separate display concerns from data storage, using normalized tables for persistence and creative display methods for user interaction.

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-Sonnet-4