Combo with Select Row Query with Multiple ID numbers.

[ilikebirds]

I have the current code for populating one combo box off another combo box:

Private Sub Combo110_AfterUpdate()
  With Me![Combo112]
    If IsNull(Me!Combo110) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [Root_Cause] " & _
                   "FROM xRoot_Cause " & _
                   "WHERE [ID]=" & Me!Combo110
                          End If
    Call .Requery
  End With
End Sub

My question is if Root_Cause has multiple Ids from table me!Combo110 can it work ?

[Stewart Ross]

Hi again.

When you reference the value property of combo Me!Combo110 you are retrieving whatever single item element is set as its current value. Unlike a list box which you can set to allow multi-select, combos return single values only.

You have already guarded aginst a null (when no selection has yet been made), so your combo will contain a value. The number of other values in the source of the combo will not affect what is selected. Your Where clause should work OK as long as the value in the combo does match an ID in your xroot_cause table; if not, no rows will be returned by your SELECT statement.

If the ID in your xroot_cause table is not unique (depending on what you mean by 'multiple IDs from the root cause table) then multiple rows will be returned by your WHERE clause as the rowsource for your next combo.

I presume you do not mean table me!combo110 (as the combo is not a table) but whatever table or query is the rowsource for combo110.

-Stewart