CODEWITHSUNDEEP
vbams-accesscomboboxmultivalue
Arne Clicteur
Arne Clicteur

Reputation: 59

Hide values in combobox with multi-value controlsource

I'm using a multi-value field to assign different criteria to a person. These criteria are grouped in different categories. I'm using a dropdown list to choose the categories, selecting one fires some VBA that changes a combobox that displays the criteria. The problem with this is that the criteria from other categories in the multi-value field show up as ID-numbers in the combobox, since the controlsource is fixed on the multi-value field. How can I hide these unwanted ID's, or is this not possible with my approach? 

Table AdmitCriteria:
   ID  |  fkCatID  |  Label
    1  |     1     |   xxx
    2  |     1     |   xxx
    3  |     1     |   xxx
    4  |     2     |   xxx
    5  |     3     |   xxx
    6  |     3     |   xxx

Table Patients:
   AdmitCrit (multi-value field)

Form:
   critCategoriesDropdown: rowsource = ID, Label FROM AdmitCriteria
   cboCriteria: controlsource = AdmitCrit
                rowsource: VBA

-

Private Sub critCategoriesDropdown_AfterUpdate()
    Dim strSource As String
    Dim ctrlSource As String

    strSource = "SELECT Id, Label FROM AdmitCriteria WHERE fkCatID = " & Me.critCategoriesDropdown.Column(0) & ";"
    Me.cboCriteria.RowSource = strSource
End Sub

Unwanted ID's at the end

Upvotes: 1

Views: 185

Answers (1)

Arne Clicteur
Arne Clicteur

Reputation: 59

Fiddled with the settings, found the option ShowOnlyRowSourceValues. This solved the problem.

Upvotes: 2

Related Questions