DataWright Information Services

Consulting and Resources for Excel and Access

Dependent combo boxes on continuous forms

It's not difficult to build two combo boxes where one shows a filtered list dependent on selections in the other combo box. There is an article on this site showing how to do it on a form which shows one record per screen.

Try the same technique in continuous forms and it doesn't work as well. The data still goes into the combos but filtering to show the combo in one record, makes the other records appear to be blank. The data is still there; it's just not visible because of the filtering.

This article shows an approach that still lets you select from a filtered list, but you get to see the data in the other records as well.

The problem: data "disappears" from filtered combos

In the sample database, open frmItems_Problem and add a few records. As you update the Contact field, only contacts from the same business as the current record can be seen. The reason is that contacts from the other businesses are not part of the combo's dataset, so they cannot be displayed.

data "disappears" in filtered combo boxes

The solution: use a popup form to do the filtering

To make all of the data visible in the Contacts field you need to remove the filter; delete the criteria highlighted in the next screenshot and all of the values are visible again.

The higlighted criteria must be removed if you want to see values for all records

Of course, that's not much use because we want to filter the contact list to simplify data entry. That's where the popup form comes in. fpopContacts is an unbound form that contains a single listbox, lstContact, with 3 columns. The first and third columns are hidden by setting their widths to 0.

Launch the form using the AfterUpdate event of the first combo

In frmItems_Solution, the Suppliers combo box has the following code in the AfterUpdate event:

Private Sub Supplier_AfterUpdate()
    DoCmd.OpenForm "fpopContacts", _
        View:=acNormal, _
        WindowMode:=acDialog, _
        OpenArgs:=Me.Name & "|Contact|" & Me.Supplier 'code pauses here while the selection is made
    DoCmd.Close acForm, "fpopContacts"
End Sub

fpopContacts launches in dialog mode, which stops the code until you close the popup. The OpenArgs is a pipe-delimited list containing the name of the caller form, the name of the control to be updated, and the value to be used as the filter.

Parse the OpenArgs string and rewrite the Row Source of the listbox

When the form opens, the OpenArgs string is parsed and used to create the SQL for the listbox.

Private Sub Form_Open(Cancel As Integer)
    Dim sSQL As String, _
        sFilter As String
    Dim lngID As Long
    Dim x
    sSQL = "SELECT tblContacts.ContactID, " _
        & "[LastName] & "", "" & [FirstName] AS Contact, " _
        & "tblContacts.BusinessID FROM tblContacts"
    If Len(Me.OpenArgs) > 0 Then
        x = Split(Me.OpenArgs, "|")
        sForm = x(0)
        sControl = x(1)
        lngID = x(2)
        sFilter = " WHERE tblContacts.[BusinessID]=" & lngID
        sSQL = sSQL & sFilter
        sSQL = sSQL
    End If
    Me.lstContact.RowSource = sSQL
End Sub

The SQL is copied from the rowsource of the Contacts field. It has been broken onto several lines to make it easier to read, and the internal quotes have been doubled up to keep the VBA parser happy.

The If statement checks to see whether there is anything in the OpenArgs, indicating that we will be filtering rows in the listbox. If anything is found it is parsed using the Split function, to give us 3 variables. The filter string is now built and appended to the starting SQL, and the rowsource of the listbox is updated.

Note: sForm and sControl are declared at the module level because their values will be used in more than one routine. If you look at the complete code in the database you will see those declarations.

When you select a contact, update the Contact field on the data entry form

The AfterUpdate event on the listbox pushes the selected value into Contact and hides the popup.

Private Sub lstContact_AfterUpdate()
    Forms(sForm).Controls(sControl) = Me.lstContact
    Me.Visible = False
End Sub

At this stage, the original form continues running its code and these two lines finish the job: updating Contact, and closing the popup form, ready for next time.

    DoCmd.Close acForm, "fpopContacts"

The finished for displays combo values for all records

That's it. There are some things that could improve the technique; make the popup launch next to the control that called it, and work out a way to adapt the SQL so that you can use the same popup form with multiple tables. But as it stands, you should find it to be an improvement.