Combobox hide values already in table (1 Viewer)

Japaner84 · Oct 18, 2019

Hello,
I have a combobox and in there you have values to choose from, but what I want is that once you have chosen a value from the box it is no longer possible to chose it again/it gets hidden in the combobox, meaning that so long as the value is saved in the table it's also hidden in the combobox.
Is there any way to do this?

NauticalGent · Oct 18, 2019

Good morning and welcome to AWF!

Got a question, most combobox options are derived from a table or query. You seem to want to have only values that are NOT in a particular table. If I understand you correctly, then where are the combobox values coming from?

arnelgp · Oct 18, 2019

if you combo is Rowsource Type is Value List, you can Remove the item from the combo.
there is no event to hide the item.
add code to the Form's After Update event.

Code:

Private Sub Form_AfterUpdate()
    Dim i As Integer
    For i = Me.ComboX.ListCount - 1 To 0 Step -1
        If DCount("1", "TableName", "[FieldName]='" & Me.ComboX.ItemData(i) & "'") > 0 Then
            Me.ComboX.RemoveItem i
        End If
    Next
        
End Sub

theDBguy · Oct 18, 2019

Hi. Welcome to AWF! I have different question. Are you using this combobox in a continuous view form?

The_Doc_Man · Oct 18, 2019

If this is a value list combo box, it wouldn't take much to empty it. If the combo box gets its values from a table of possible values, you could define the .RowSource query that drives the combo box to include a WHERE clause, ...

Code:

WHERE value NOT IN ( SELECT usedvalue FROM usedvaluetable )

Pat Hartman · Oct 21, 2019

The rowsource for your combo can include a join to the table/query the form is bound to. Use a left join and WHERE criteria.

Where FKfieldInFormsTable is Null.

Combobox hide values already in table (1 Viewer)

Japaner84

New member

NauticalGent

Ignore List Poster Boy

arnelgp

..forever waiting... waiting for jellybean!

theDBguy

I’m here to help

The_Doc_Man

Immoderate Moderator

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread