For example, a vendor might use a series of combo boxes to find customers in a specific city by first selecting a State from a combo box bound to a Lookup table of States. That selection filters the City list in a combo box bound to a table of Cities. And that, in turn filters the list of customers to those whose address is found in the city selected in the City combo box.
Some time ago, I decided to see if I could simplify the process by using TempVars as the criteria for those combo boxes and forms. It turns out that there are a couple of advantages to using TempVars instead of more traditional approaches.
Dynamic SQL
One common approach is to dynamically re-write SQL Statements for "downstream" combo boxes to apply the filter selected in the "upstream" combo box. Something like this:strSQL = "SELECT CityName FROM tblCustomerAddress
WHERE StateName = '" & Me.cboSelectState & "'"
Me.cboSelect.RowSource = strSQL
Form and Control References in Queries
Another approach is to incorporate a form control reference directly in the query, like this:SELECT CityName FROM tblCustomerAddress
WHERE StateName = Forms!frmCustomers.cboSelectState
In both cases, as long as the form is open, the downstream, city, combo box will properly return the list of cities when it is requeried after a selection in the upstream, state, combo box.
TempVars
I decided that I could do better. Instead of dynamic SQL or hard-coded form and control references, I could write queries that filter on TempVars. That gave me two advantages.First, there's no need to fuss with writing and rewriting SQL dynamically. The rowsource of the downstream combo box, as well as the recordsource of the form, could be written as saved queries that never need to be modified at run time.
Second, because the TempVar can be set or modified as needed, the queries can be opened directly from the Navigation Pane whether the form is open or not. TempVars don't lose their values until some event in the Relational Database Application changes them. And that can make trouble-shooting just a little bit less fussy.
All or One
In addition to the flexibility and ease of use of TempVars, I found an additional tool to make Cascading Combo boxes even more useful. By wrapping the TempVars in an Immediate If expression, I could select ALL records or only ONE record without having to do any additional coding. You can download a sample Access Relational Database Application using this method here: Download of Cascading Combo Box From GPC Data. |
| Design View of Form's RecordSource Query with Iif() |
 | |
| SQL View of Form's RecordSource Query with Iif() |
Here's how it works. There are two possibilities.
The value of TempVar can be 0 or it can be a positive, non-zero long integer. This is set in the AfterUpdate() event of the combo box on the form.
If the TempVar is 0, the Iif() expressions in the criteria select records where 0 = 0. In other words, ALL records will be returned.
If the TempVar is not 0, the Iif() expressions select records where the WorkID is equal to that TempVar value, which will be one record.
All we need to do is set the value of the TempVar in the AfterUpdate() Event and requery the form.
Private Sub cboSelectWork_AfterUpdate()
With Me
TempVars.Add Name:="lngWorkID", Value:=Nz(.cboSelectWork, 0)
.Requery
End With
End Sub
One more trick makes it possible to return ALL records, if the user wants to see them, but we don't bog down the form by automatically doing so.
Here's the SQL from the combo box' rowsource:
 |
| UNION Query to Return ALL Records or ONE Record |
 |
| Cascading Combo Box Filtered to Work for One Custumer |
One Customer, Darleve Sucknere, has been selected in the upstream combo box for customers. This sets the TempVar that filters the downstream combo box for Work. It shows three projects done for that client, PLUS the option to select All Work for All Customers.
Download and Play with the Demo
The Cascading Combo Box with TempVars Demo shows all of the features called out above, plus a few more enhancements you might like.
Let me know what you think.
