Auto reduce the items in list in Combo Box

  • Thread starter Thread starter Silvio
  • Start date Start date
S

Silvio

I have a combo box with the following Row Source in oder to enter new records:

SELECT tblSubTrades.SubTradeID, tblSubTrades.SubTradeDesc
FROM tblSubTrades
ORDER BY tblSubTrades.SubTradeDesc;

How can eliminate from the combo box items that have been used already in
order to populate the table? In few words, if combo box has Red, Orange,
White, and Black in the list, once I select for example Orange from the list
to create a new record then my option should be only Red, White, and Black
when I try to crate the next record.
 
I have a combo box with the following Row Source in oder to enter new records:

SELECT tblSubTrades.SubTradeID, tblSubTrades.SubTradeDesc
FROM tblSubTrades
ORDER BY tblSubTrades.SubTradeDesc;

How can eliminate from the combo box items that have been used already in
order to populate the table? In few words, if combo box has Red, Orange,
White, and Black in the list, once I select for example Orange from the list
to create a new record then my option should be only Red, White, and Black
when I try to crate the next record.
Click to expand...

This is not altogether correct, but something along these lines.

Private Sub Form_Current()

Dim s As String

' build dynamic row source to show - only - records
' not yet used

s = "SELECT SubTradeID, SubTradeDesc " & _
"FROM tblSubTrades " & _
"WHERE SubTradeID " & _
"NOT IN (SELECT SubTradeID " & _
"FROM [Table that is recordsource of form] " & _
"WHERE [table key column] = '" & Me.[ key column] & "');"

' set row source to query string
Me.cbo_SubTradeID.RowSource = s

end sub
 
Back
Top