How to filter second combo box by selection in first combo box

[mbedford]

In my asset and employee database I have Departments and Subdepartments.

In the forms I want to filter the Subdepartment combo boxes based on the selection in the Department combo box.

Combo boxes:
cboDept
cbosubDept

Source tables and fields:
listDept
- ID (primary key, not really used currently)
- Dept (Department acronym)
- Name (Full department name, not really used)
listSubDept
- ID (primary key, not really used currently)
- Dept (Department acronym from listDept)
- SubDept (Full name of Subdepartment)

Source for cbosubDept:

SELECT listSubDept.SubDept
FROM listSubDept
ORDER BY listSubDept.SubDept;

Source for updating cbosubDept:

Private Sub cboDept_AfterUpdate()
    ' Filter records in cboSubDept based on selection in cboDept
    Dim subDeptSource As String
 
    subDeptSource = "SELECT[listSubDept].[SubDept],[listSubDept].[Dept] " & _
        "FROM listSubDept " & _
        "WHERE [Dept] = " & Me.cboDept.Value
    Me.cbosubDept.RowSource = subDeptSource
    Me.cbosubDept.Requery
 
End Sub

I've adapted this code from here:
http://www.databasedev.co.uk/filter_combo_boxes.html

Explanation:
I'm using the full Dept for reference because currently I haven't become comfortable or found a consistant method of viewing one (user-friendly) value in a form while storing a different (numeric) value in the table.

Problems:
1) When I select a Dept for which there are no SubDepts, cboSubDept shows all SubDepts. I know I don't actually have any code regarding how to deal with a Dept for which there are no SubDepts, and I would appreciate pointers along those lines.

2) When I select a Dept for which there are SubDepts, I get a pop up asking for Parameter Values. Do I need to switch to the listDept.ID in listSubDept and in the Form in order to make this work? Or is something else causing this issue?

[mbedford]

I would also like to add some If/Then logic to the cboDept to change the behavior of cbosubDept based on whether or not there are subDepts for the selection in cboDept.

Something along the lines of:

Private Sub cboDept_AfterUpdate() 
    ' Filter records in cboSubDept based on selection in cboDept 
    Dim subDeptSource As String 
 
    If Me.cboDept.Value (does not equal null)
    subDeptSource = "SELECT[listSubDept].[SubDept],[listSubDept].[Dept] " & _ 
        "FROM listSubDept " & _ 
        "WHERE [Dept] = " & Me.cboDept.Value 
    Me.cbosubDept.RowSource = subDeptSource 
    Me.cbosubDept.Requery 
 
     Else
       (grey out, disable, hide, or display string "N/A" in cbosubDept)
    EndIf
 
End Sub 
 
 

I suppose I could set the initial state of cboSubDept.Visible to No.
But if I were opening the form, would it default to the visible state based on the intially selected record or the visible state defined in the control properties?

Or do I need to add code to the form determining the initial visible state based on the initial entry selected?

[colintis]

To your where clause, try putting double quotes between the value

"WHERE [Dept] = "  & Chr(34) & Me.cboDept.Value & Chr(34)

Note: Chr(34) is the "

For the if statement, there's a function called ListCount, try put the if statement as this:

If Me.cboDept.ListCount = 0 Then
      (code for the stuffs)
Else
      Me.cboDept.Enable = False  'This should disable the combo box
      Me.cboDept.Visible = False 'This will hide the combo box
End If
 

Also if you want to check the amount of items in the combo box, you should put the rowsource codes outside the if statement, so that it can do the counting after the 2nd combo box is updated.

[NeoPa]

Check out Example Filtering on a Form and tell us if there are any issues outstanding. This should cover the fundamentals, and progress from there should be simpler and clearer.

Welcome to Bytes!