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:
- Open the form
- Select your state from the combo box, which fires the combo box After Update Event
- 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:
- A place to store the last selection
- Code to Store the last selection
- Code to retrieve that last selection
- 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.
