Filtering combo boxes from a list of names in a table (1 Viewer)

[seeker9969]

I have a table with a list of names.
on the form I have 4 combo boxes all pulling from the same table. Is there a way to remove a name from the next combo box once its been picked in one of the other combo boxes?

 

[Micron]

What you want is commonly referred to as cascading combo boxes. Lots of posts everywhere on the subject so no real need on how to reproduce the behaviour here, I think. At least you now know what to search on?

 

[MajP]

This one is a little more complex IMO since you can select from the combos in any order. I had to try to demo

Public Function StrNames()
  Dim ctrl As Access.Control
 
  For Each ctrl In Me.Controls
    If ctrl.Tag = "?" Then
      If Not IsNull(ctrl) Then
        If StrNames = "" Then
          StrNames = "'" & ctrl & "'"
        Else
          StrNames = StrNames & ", '" & ctrl & "'"
        End If
      End If
    End If
  Next ctrl
 
  If StrNames <> "" Then
    StrNames = "(" & StrNames & ")"
  Else
    StrNames = "('Something not in your list')"
  End If
 
End Function

Private Sub cmboName_Enter()
  Dim strSql As String
  strSql = "Select Full_Name from tblData where Full_name not in " & StrNames & " ORDER BY Full_name"
  Debug.Print strSql
  cmboName.RowSource = strSql
 
End Sub

Private Sub cmboName2_Enter()
  Dim strSql As String
  strSql = "Select Full_Name from tblData where Full_name not in " & StrNames & " ORDER BY Full_name"
  'Debug.Print strSql
  cmboName2.RowSource = strSql
End Sub

Private Sub cmboName3_Enter()
  Dim strSql As String
  strSql = "Select Full_Name from tblData where Full_name not in " & StrNames & " ORDER BY Full_name"
  'Debug.Print strSql
  cmboName3.RowSource = strSql
End Sub

Private Sub cmboName4_Enter()
  Dim strSql As String
  strSql = "Select Full_Name from tblData where Full_name not in " & StrNames & " ORDER BY Full_name"
  'Debug.Print strSql
  cmboName4.RowSource = strSql
End Sub

 

[Micron]

@MajP, that's impressive. Would/could you not also just select all the combos in design view and set their Enter property to call one function that in turn calls strName? Or is 4 combos not enough (number wise) to go that route, in your opinion?

 

[MajP]

hat's impressive. Would/could you not also just select all the combos in design view and set their Enter property to call one function that in turn calls strName? Or is 4 combos not enough (number wise) to go that route, in your opinion?

I was going to do that, but thought the OP might have a harder time following if they had not seen that technique before.

 

[MajP]

Public Function StrNames()
  Dim ctrl As Access.Control
 
  For Each ctrl In Me.Controls
    If ctrl.Tag = "?" Then
      If Not IsNull(ctrl) Then
        If StrNames = "" Then
          StrNames = "'" & ctrl & "'"
        Else
          StrNames = StrNames & ", '" & ctrl & "'"
        End If
      End If
    End If
  Next ctrl
 
  If StrNames <> "" Then
    StrNames = "(" & StrNames & ")"
  Else
    StrNames = "('Something not in your list')"
  End If
 
End Function

Private Function FilterCOmbos()
  Dim ctrl As ComboBox
  Set ctrl = Me.ActiveControl
  ctrl.RowSource = "Select Full_Name from tblData where Full_name not in " & StrNames & " ORDER BY Full_name"
End Function

in the onEnter of the combox
=FilterCombos()

 

[seeker9969]

Thats awesome MajP I wondered in the table you were pulling the data from it has 6 fields are those necessary? As the table I want to pull from only has the Id field and the name field. Thank you very much that will work great.

 

[MajP]

MajP I wondered in the table you were pulling the data from it has 6 fields are those necessary?

They are not necessary, but they are not used either so makes no difference. The combo boxes are based on a query that returns only one field. Your queries will have to be modified to match your names. Are you wanting to filter on the name or the id. If you are doing the ID which is numeric than you have to lose the single quotes
StrNames = "'" & ctrl & "'"
becomes
StrNames = " & ctrl