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

Smart Combo Boxes — Auto Select Your Last Selection Then Go One Step Beyond to Fire the Combo Box Event

In an earlier career, I was a school psychologist. In that role, I frequently gave intelligence tests to students in order to get some indication of what they should be able to achieve academically, as well as to tease out cognitive strengths and weaknesses. Once that was complete, I then had to write a report of the results.

Although a critical factor in writing these reports was analysis and recommendations, much of the report creation was mechanical: scoring, descriptions of sub-tests where the student had done well or poorly and the educational implications of the relative strengths and weaknesses of the results.

This mechanical part was time consuming and easy to get wrong. To solve this problem, I wrote a computer program that would generate the mechanical stuff, which left me free to focus on the analysis and recommendations for the student. The end goal was to remove the repetitive mechanics from the process.
 

The Need

In a like manner, there are many user processes that are repetitive when you first open an Access form. You, or your user, must make some selection in a combo box (also known as a drop-down box) in order to get to the record or set of records where you want to work.

In many cases, the selection you make is the same as the selection you made the last time.  For instance, you have a combo box of U.S. states that allow you to filter the offices in a given state so that you can focus your work. This week you are focused on Texas, and you want to continue with Texas until you are finished with that piece of the project, and then you want to focus on offices in Colorado
.
In each case, you will be working with a state for a period of days.  Unless you have done something to make things happen automatically, the process is
  1. You open the form,
  2. You select your state from the combo box, which fires the after update event,
  3. The form gets filtered to your selection.
Wouldn’t it be nice if, upon opening the form, the results in front of you were consistent with the last time you made a selection in the combo box? This is what we will do in this month’s Wizard – Smart Combo boxes that auto select your last choice.
 

The Solution

To make this happen, you need only four things:
  1. A place to store the last selection
  2. Code to Store the last selection
  3. Code to retrieve the last selection
  4. Code to fire the after update event of the 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. If you are not familiar with this approach, take a look at the February 2016 Wizard.

As part of the after update event of the combo box, simply add a line of code that stores your selection.

Let’s say the user selected “Texas” in the combo box. You code should say something on the order of

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

See February 2016 Wizard for the code I use to store and retrieve these variables. Some developers use forms. Although you may be tempted to use TempVars (which are very powerful tools), these won’t work because the value of the TempVars are lost when your program closes.

To retrieve the last selection, see the Wizard above for sample code.
 

The Payoff

Let’s assume the following:
  • Your form is called frmOffices
  • Your State combo box is called cboState
  • You have stored the value in the admin Table with an identifier of ‘'Selected State'’
  • Your combo box already has the code it needs to filter the form once the user makes a selection
For this process to work, your form must have an open event. To check if one exists, open the form in design view and make sure that the form itself is selected by clicking on the upper left hand corner of the design view to make sure it has a black rectangle (as seen below). Click on the event tab, then tune in to the On Open Event.



This event contains code that runs when the form is opened. If you have existing code, you just need to add to what you already have. If not, then click on the ellipsis and choose code, and you will be brought to a VBA code window for the Form open event. From here, add the following lines:

Private Sub Form_Open(Cancel As Integer)
    Dim strState As String
    strState = DLookup("fldValue", "zhtbladmin", "fldItem = 'Selected State'")
    Me.cboState = strState
    cboState_AfterUpdate
End Sub


This code does a straightforward lookup in the admin table to retrieve the value of the last selected state from the Admin table, puts that value into the State Comb box, and the fires the after update event of the combo box. Essentially, this mimics what the user would do if s/he were to enter the last used state.

On the after update event of the State combo box, add the following line of code:

Private Sub cboState_AfterUpdate()
    Dim strSQL As String
    Dim strState As String
    Existing code to filter on the selected state should be here
    strState = Me.cboState
 
    strSQL = "UPDATE zhtblAdmin SET zhtblAdmin.fldValue = '" & strState & "'" & _
         " WHERE zhtblAdmin.fldItem= 'Selected State'"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub
 
This routine creates 2 variables:
    strSQL – this holds a sQL statement
    strState –  this holds the value the user has chosen
 
The line strState = Me.cboState fills the state variable with the select state.
 
The line strSQL = … takes the value selected and puts it into the Admin table in the row that contains the fldItem field = ‘SelectedState’ (see February 2016 Wizard for details.
 
At this point, the value has been stored for the next time the form opens, at which point it will retrieve that value, put it into the combo box, and automatically filter the form.
 

Conclusion

This month, we have automated a process that tends to be tedious. Although it takes a bit of work to set up, you now have a more user-friendly and efficient application. Steps like this, when applied across a project, result in less time spent in manual processes and more time for the user to focus on more important issues.
 

Tip of the Month: Zoom In to Read Text


If you are having trouble reading the text in a text box, you can temporally increase the font.


In the textbox, hold down the shift key and press the F2 key.

This brings up a Zoom box with the contents of the text box. In the Zoom box, there is a font button. Clicking the font button will change the font to whatever size you choose, making small print as big as you need to read it easily.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

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