Master Lookup Form
The Master Lookup Form is the hub of my Access applications.
Many Access applications have a main table that the rest of the application revolves around.
This example comes from a program I wrote for a county Adult Probation department. After a defendant is found guilty of a crime in the United States, they meet with a probation officer who prepares a report for the judge to use during sentencing. This is known as a Pre-Sentence Investigation (PSI) report.
The main table in this application is the one that holds these PSI records.
Master Lookup Form Screenshot
Here's what the form looks like:
Items of Note
I'd like to highlight several features of this particular style of form.
Read-Only Form
Users cannot make changes to the data directly on this form.
This frees us to use a non-updateable, aggregate query as the RecordSource for the form.
Active Row Highlighting
I use my HighlightRow() function to show the active row in a yellow highlight.
Opening a Record for Editing
To edit a record, the user double-clicks anywhere on the yellow highlight.
To implement this feature, I use a transparent button that is the same size as the highlight text box and sits on top of it in the z-order. The DblClick event of this transparent button contains code to open the PSI form for editing.
HTML-Style Hyperlinks
The "PDF" and "Face Sheet" items are actually label controls with transparent buttons overlaid on top of them. The Cursor On Hover setting for the transparent buttons is "Hyperlink hand." Unlike the double-click required to open an existing record for editing, these buttons respond to a single-click event in keeping with the HTML paradigm.
Keeping these commands in-line with each record makes it obvious that these commands belong to the record displayed on that row.
Custom Filtering
I like to have full control over my user interface, so I almost never use datasheets on my forms.
This does come with the downside that I have to roll my own filtering. I use the AfterUpdate event of each filtering text box at the bottom of the form to update the form's record source. I use labels with a Special Effect of "Shadowed" to create the filter backgrounds. The Back Color of active filters is set to Green to make it obvious to the user that they are restricting the data in the form based on that filter.
The red "X" in the upper corner of each filter allows the user to clear that filter with a single click. I set Cursor On Hover to "Hyperlink hand" to make it more obvious to the user that clicking on that icon will do something.
Default Filtering
Whenever possible, I use a default filter to show the users a small number of highly relevant records when the form loads.
In this case, I set the "Investigator" filter equal to the current user's login and the "Sentenced After" filter to 30 days in the past. This restricts the form to show only those PSIs that recently went for sentencing (or are scheduled for sentencing in the future) for which the current user is responsible.
This ensures the form loads quickly, shows the most relevant info, and provides new users with a sample of what an active filter looks like.
Adding New Records
The [Add New PSI] button opens an unbound form for the user to create a new PSI record.
Displayed at Program Startup
Rather than show a Main Menu form, I use the ribbon for navigation in most of my new development work.
Since most users who open this program do so with the intent to work on an upcoming PSI, I simply show this form right at program startup.
Referenced articles
Mike Wolfe
Mike Wolfe
