The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Smart Combo Boxes – Autoselect Your Last Selection and Fire the After Update Code

In an earlier career, I was a school psychologist. In that role, one of my jobs was to administer intelligence tests to students to assess their general ability to learn. Although giving the tests was an interesting exercise, writing the results was fairly standard except for a few areas that called for judgment.

To make this process easier, I wrote a program that took in the results of the tests and produced a significant portion of the report. These sections of the report were mechanical in nature and required no judgment.

What made the program so nifty is that, not only did it save time, but it also automatically opened a specific Word document to start a new report. Once loaded, this report was ready for inputs from me to start a standard analysis. It was the early days of desktop computers and, though the program I wrote was not terribly sophisticated, it saved me a considerable amount of time.

In this month’s Wizard, I will show a similar method of setting up a form that anticipates your needs; it will save you time and make your life easier:  A smart combo box. 
 

The Need

In many instances when you first open a form, you or your user must make a selection in a combo box in order to bring up a record or set of records that you are interested in working with.

In many cases, the selection you make is the same as the one you made the last time.  For instance, you have a combo box of U.S. states that allows you to filter the offices in the state you selected. The last time you were in the form, you focused on Texas. Now you want to continue with Texas until you are finished, then you want to focus on your next state. You know you will be working with a state for a period of days.
 
The basic steps are:
  1. Open the form
  2. Select your state from the combo box, which fires the combo box After Update Event
  3. Form gets filtered to your selection.
Wouldn’t it be nice if, on opening the form, the results in front of you picked up where you left off the last time you made a selection in the Combo box? That is what we will do in this month’s Wizard. Our goal: A Smart Combo Box that auto selects your last choice.
 

The Solution

To make this happen, you need 4 things:
  1. A place to store the last selection
  2. Code to Store the last selection
  3. Code to retrieve that last selection
  4. Code to fire the After Update Event combo box code.
I recommend the use of a dedicated admin table to store the selection. With this approach, any stored value you may need is available from all parts of your program. To learn more about a dedicated admin table, see February’s Wizard.

As part of the after update event of the combo box, add a line of code that stores your selection. For example, let’s say the user selected “Texas” in the combo box. Your code should be something like the following:

DoCmd.RunSQL  “Update tblAdmin  set AdminItemValue = ‘Texas’ where AdminItem = ‘Last State Selected’”

This will store the value “Texas” in the AdminItemValue in the row with the Admin Item = “Last State Selected”

To get the your choice back, use the following:

YourVariable = dlookup("fldValue","zhtbladmin","fldItem = ‘Last State Selected’ ")

Let’s assume your combo box is named cboStates. In the open event of your form, use following code:

Private Sub Form_Open(Cancel As Integer)
    Dim strState as string
    strState = dlookup("fldValue","zhtbladmin","fldItem = ‘Last State Selected’ ")
    me.cboState = strState
    cboState_AfterUpdate

End Sub

Now when your form opens, it will retrieve the last state you used from the Admin table, put it into the combo box, fire the After Update Event of the combo box, and take you back to where you were when you last used the form. 
 

Conclusion 

This month, I showed you how to make your combo box auto-populate with your last selection, and set you up to start working with little effort: One more efficiency that will help you to more with less effort.

Tip of the Month: How to Fire an Event without the Event

In this month’s Wizard, I directed you to fire the After Update Event of a combo box. We did this by firing the code associated with the combo box event “After Update.”

This concept can be extended to all events that take place in your Access Application, provided your code can see the event code. Any form (or report) can “see” all code associated with itself. This means that any event in the form/report can be fired with the code:

ControlName_EventName

So if you want to fire the event associated when a checkbox is checked or unchecked, for example if you have a check box named “chkON,” your code would say

chkOn_AfterUpdate

It’s that simple. What makes this especially easy is that, if you’re not sure of the code to call, simply create the event and in the code window and copy the first line of the created event, removing the “Private Sub “ in the beginning and the “()” end.


Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886