Auto reduce the items in list in Combo Box

[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.

 

[Michael Gramelspacher]

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.

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