Drilling with Combo Boxes

<< Click to Display Table of Contents >>

Navigation:  Combo Boxes and List Boxes >

Drilling with Combo Boxes

Jeff Ruffner          
Jeff provides a comprehensive look at how you can use your combo boxes for more than just selecting values.

At the end of 1998, there were two articles in Smart Access about using combo boxes in Access to select the record to be displayed in a form. These articles were triggered by the Access Bookmark Bug, to which the code generated by Access 97's Combo Box Wizard fell prey (see "An Access Nightmare" by Andy Baron, November-1998, and "Cloning Solutions" by Garry Robinson, December-1998). Garry's techniques (and the various upgrades and patches also listed in Smart Access) handle the bug, but there are still some problems with using a combo box to select records. In this article, I'm going to demonstrate those problems, look at solutions, and then show you how to use combo boxes to create a powerful and intuitive "drill-down" user interface for displaying a hierarchical data structure.
I've included a sample database in the accompanying Download file that demonstrates the problems that can occur when you use a combo box. The database contains a form called Combo Box Demo, which consists of two combo boxes, two text boxes bound to table Combo Demo, and an Add Record command button (see Figure 1). The first combo box was made with the Access Wizard, while the second one reflects my enhancements. Both combo boxes display a field (I'll call it the selector field) that can be used to find records for the form to display.

199903_JR1 Figure 1

To demonstrate the first problem with the combo box record selection technique, try selecting a record with the Wizard combo box in the sample form. Then, using the record selector at the bottom of the form, change the current record. The Wizard combo box still shows the record you selected, even though the rest of the form shows the data from the record you moved to. This can be very confusing for users if they expect the value of the combo box to match the rest of the form. My enhanced combo box does display the correct value because I've added the following code to the Current event of the form:

Private Sub Form_Current()

    Me!cboBranchSelect = Me!BranchName

End Sub

This code simply sets the value of the combo box to the current value of the selector field. In fact, to avoid further confusion, I usually set the Visible property of the selector text box to False. This ensures that the only visible box on the form (the one used to select records) shows the data for current records. However, this creates another problem: The user can't change the value of the selector field because the combo box isn't bound to the field. For a lot of applications, especially browsing, this isn't a problem. If you do need to update the selector field, you can add a command button to the form that can be used to set the Visible property of the selector field's text box to True. You can set the selector's text box Visible property back to False in the control's After Update event.

The second problem occurs when records are added after the form is opened. The added records aren't displayed in the combo box's list because the list is populated when the form is opened. To demonstrate this, click the Add Record button in my sample form and enter data in the two fields. With that done, move to another record to save your new record. Now, click on each combo box. The new record is included in the enhanced combo box but not in the Wizard combo box.

My solution was to force the combo box to repopulate its list each time it's opened. The most flexible way to do this is to add the following code in the combo box's Got Focus event:

Private Sub cboBranchSelect_GotFocus()

  Me!cboBranchSelect.RowSource = "SELECT " _

        & "[Combo Demo].BranchName FROM " _

        & "[Combo Demo]"

End Sub


I like this technique (as opposed to doing a Requery) because it allows me to modify the SQL statement if I want to. At this point, the key thing is that each time the combo box receives the focus, its list box is repopulated with the records from the Combo Demo table. In the next part of this article, I'll show how to take advantage of resetting the RowSource property to build a drill-down form.
The drilling project
The application I'm using to demonstrate this technique tracks the maintenance needs of all the equipment in my company by organizational hierarchy. These routines use the technique of resetting the RowSource to dynamically rebuild the combo boxes. Figure 2 shows the project data model. The company is divided into branches. Each branch has one or more plants, which in turn are composed of one or more systems. Each system interfaces with other systems. Similar systems in different plants may have the same name, so to locate a particular system, you have to know the branch and plant in which it's located. The data, while stored in relational tables, is really hierarchical. Hierarchical data models exist in numerous applications, including organizational charts, family trees, "top down" programming, and, when you think about it, the Access object model (DAO) itself.

199903_JR2 Figure 2
A "drill-down" form allows you to start at the highest level of the hierarchy and work your way down to the details. Figure 3 shows a drill-down form for the tables shown previously (I've also included it in the download database). The form consists of:

a combo box for each of the first three levels: Branch, Plant, and System

a bound description text box for each field

a subform to list the interfaces for the currently displayed system

a text box to display the List Count property for each combo box (the List Count property gives you the number of items in the combo box's list)

three hidden text boxes, one bound to each of the three search fields: BranchName, PlantName, and SystemName.

199903_JR3 Figure 3

The form's Record Source is the qryDrillDown query that links branches, plants, and systems, as shown in the project's data model.
The only form-level code is contained in the form's Current event. This code assures that the three combo boxes show the correct values contained in the three hidden fields:

Private Sub Form_Current()

    Me!cboBranchName = Me!BranchName

    Me!cboPlantName = Me!PlantName

    Me!cboSystemName = Me!SystemName

End Sub


The code for the GotFocus event of the cboBranchName combo box is as follows:

Private Sub cboBranchName_GotFocus()

    Me!cboBranchName.RowSource = "SELECT " _

      & " DISTINCTROW Branch.BranchName, " _ 

      & " Branch.BranchID FROM Branch;"

    Testbox = "Branch :" & _

        Me!cboBranchName.ListCount & " Plant:" _

        & Me!cboPlantName.ListCount & _

        "  System:" & Me!cboSystemName.ListCount

End Sub


The code for the cboBranchName sets the Row Source of the combo box to a SQL statement that returns a list with two columns: the branch name and the corresponding branch ID number. The column width of the second column is set to zero, so only the branch name is displayed. The code also prints the ListCount property of each combo box in the Testbox control.
The code for the GotFocus event of the cboPlantName control is similar:

Private Sub cboPlantName_GotFocus()

    Dim Temp As String


   If Me!cboBranchName.ListCount = 0 Then

      Temp = "SELECT DISTINCTROW "& _

       "Plant.PlantName, Plant.PlantID FROM " & _

       "Plant WHERE Plant.BranchID = 0"


      Temp = "SELECT DISTINCTROW " & _

       "Plant.PlantName, Plant.PlantID FROM " & _

       "Plant WHERE Plant.BranchID = " 

      Temp = Temp & _

        Me!cboBranchName.Column(1, _

          Me!cboBranchName.ListIndex) _

        & ";"

    End If

      Me!cboPlantName.RowSource = Temp

      Testbox = "Branch :" & _

        Me!cboBranchName.ListCount _

         & "  Plant:" & _

        Me!cboPlantName.ListCount & " System:" _

        & Me!cboSystemName.ListCount

End Sub


The code starts by calling the GotFocus event of the cboBranchName control to ensure that its list is properly populated. The code then tests to see whether cboBranchName.ListCount is greater than zero. If it isn't, then the string variable Temp is set to a dummy SQL statement that produces no records. Otherwise, Temp is set to a statement that returns the correct records. In this case, "correct" means the plants for the branch currently selected in the cboBranchName combo box, using the ListIndex property. Finally, cboPlantName's RowSource property is set to the Temp string, and the test box is updated.
The GotFocus event for the third combo box continues to build on the hierarchical structure of the form:

Private Sub cboSystemName_GotFocus()

Dim Temp As String


  If Me!cboPlantName.ListCount = 0 Then

    Temp = "SELECT DISTINCTROW " & _

         "System.SystemName,System.SystemID " & _

         "FROM System WHERE PlantID = 0"


    Temp = "SELECT DISTINCTROW " & _

        "System.SystemName, System.SystemID " & _

        "FROM System WHERE PlantID = "

    Temp = Temp & _

           Me!cboPlantName.Column(1, _

           Me!cboPlantName.ListIndex) & ";"

  End If

  Me!cboSystemName.RowSource = Temp

  Testbox = "Branch :" & _

      Me!cboBranchName.ListCount & "  Plant:" _

      & Me!cboPlantName.ListCount & "  System:" _

      & Me!cboSystemName.ListCount

End Sub


The GotFocus event calls the cboPlantName_GotFocus event routine (which, in turn, calls cboBranchName_GotFocus) so that all three combo boxes have the most recent data. Again, this step is required because each combo box depends on the ListCount property of the previous one to check that records exist. This code checks the number of items in cboPlantName to make sure a valid plant exists. Finally, it sets the SQL clause, assigns it to the combo box's RowSource, and updates the Testbox control.
Doing the work
The AfterUpdate events for the three combo boxes do the real work. The code for BranchName.AfterUpdate and PlantName.AfterUpdate change the underlying query of the form's Record Source property, while the code behind the SystemName combo box finds the correct record with the DoCmd.FindRecord method.
Here's the code for the BranchName combo box:

Private Sub cboBranchName_AfterUpdate()

    Dim Temp As String

    Dim qrydef As QueryDef

    Dim dbs As Database

    Set dbs = CurrentDb()

    Set qrydef = dbs.OpenQueryDef("qryDrillDown")


    Temp = "SELECT DISTINCTROW Branch.*, " & _

        Plant.*, System.* " & _

  "FROM Branch  LEFT JOIN " & _

        "(Plant LEFT JOIN System ON " _

        "Plant.PlantID = System.PlantID)  " & _

        "ON Branch.BranchID = Plant.BranchID " & _

        "Where ((Branch.BranchID = " & _

        Me!cboBranchName.Column(1,  _

            Me!cboBranchName.ListIndex) & "));"


    qrydef.SQL = Temp


    Me.RecordSource = "qryDrillDown"


End Sub


As you can see, I use the information in the combo boxes to create a query that controls what the form displays. The query uses a Left Join on the Plant and System tables. The Left Join ensures that the record from the Plant table will be inserted into the recordset even if no systems exist for the selected plant. (See Peter Vogel's article, "All Things Not Being Equal," in the June 1997 issue of Smart Access for a discussion of the Left Join.)
The query uses a second Left Join to join the Branch table with the results of the first join. Again, the Left Join makes sure that at least one record is put in the recordset. If I'd used the inner join, and there were no plants associated with the branch or no systems associated with the plant, the query would have returned no records. With no records returned, there would be no data in the BranchName field, and the Branch combo box would be blank. Finally, I set the form's Record Source to the new qryDrillDown that I created to get the form to display the updated data. The queries for cboPlantName.AfterUpdate and BranchName.AfterUpdate are similar and can be found database that's available in the accompanying Download file.
The code for cboSystemName.AfterUpdate is a little simpler than the other combo boxes:

Private Sub cboSystemName_AfterUpdate()

    Dim dbs As Database

    Set dbs = CurrentDb()


    If Me!cboSystemName <> "" Then

        txtSystemName.Visible = True


        DoCmd.FindRecord Me!cboSystemName



        Me!txtSystemName.Visible = False

    End If


End Sub


Again, I start by making sure a system was selected in the combo box. If a system exists, I set the bound control, txtSystemName, to visible and give it the focus. The DoCmd.FindRecord locates the record based on the selected system, and I set the focus back to the combo box. This shifting back and forth is required because the search must be done on a bound control, but I don't want to display the SystemName text box along with the SystemName combo box. Finally, I make the text box invisible again. In the sample database, I placed the text box to the right of the other fields, so you'll see some screen flicker as the text box appears and disappears. In an actual application, I put the text box behind the combo box so the flicker is hidden.

Combo boxes can be a very powerful way to let your users select records. However, I feel that it's important to manage the user interface to ensure that the combo box shows data for the correct record and that the combo box's list shows all active records. Once you've mastered that, by changing the form's RecordSource property and using the combo box's RowSource property, you can use combo boxes to retrieve records or sets of records in ways that are intuitive for your users. And what's intuitive for your users translates into an accepted user interface for you.

Your download file is called ACCLIB.ZIP  in the file SA199904_down.zip

This can be purchased with all the other downloads on this page



Other Related Pages

Using List Regions with Many-to-Many Relationships

Auto-Generated Update SQL

Access Subquery Techniques

Tricks With Combo Boxes