Unanswered: Filtering one combo box's list using another's selection
On a form I have two combo boxes: cmbCompany and cmbContact. The cmbCompany combo box lists all entries in my table 'companies'. After the user has selected a company, I want the cmbContact box to display all contacts (from table 'contacts') where [contacts][company] = [company][id] based on the selected company.
I have tried using this for the cmbContact box:
SELECT contacts.id, contacts.last_name & ", " & contacts.first_name AS name FROM contacts WHERE company=forms!frmProjects.cmbCompany;
SELECT contacts.id, contacts.last_name & ", " & contacts.first_name AS name FROM contacts WHERE company=forms!frmProjects.cmbCompany.value;
Here is a way to have a second combo box (perhaps listing products) based on another combo box (perhaps listing companies). Thus after the user chooses a company, he then sees only the products for that company in the second combo box.
For criteria in the 2nd combo box (and to get to the query design mode of the combo box click on the 3 dots just to the right of the combo box's RowSource property):
And for the OnEnter property of the 2nd combo box:
And in any module:
Public Function ctlRequery()
' Purpose: Use this in OnEnter property of a combobox that's based on another control
' that may have changed or based on records that may have changed
' since the form was first opened.
On Error GoTo Err_ctlRequery
' Dim var.
Dim ctl As Control
' Set var.
Set ctl = Screen.ActiveControl
' Requery control.
For an example of dependent combo boxes you can download my sample invoices database currently at http://www.bullschmidt.com/access and then on the Invoices Dialog check out the Invoice # combo box which is based on the Rep # combo box.
One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown.