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

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
    Me.Contact.Requery
    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
    Else
        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.

    Me.Contact.Requery
    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.