Access VBA: How to count items of a ComboBox?

Question

In my ComboBox I have some items already selected.

I would like to count them with VBA. I was expecting to find something like the following string, but I get Compile error: Argument not optional.

Me.<ComboBox_name>.ItemData.Count

I also thought to use the following string but it gives me 0 items:

Me.<ComboBox_name>.ItemSelected.Count

Solution 1

Comboboxes are generally used for selecting or showing the selection of a single item, whereas listboxes naturally support multiple selections.

That said, if you're linking a multivalued* table field to a Combobox, for example, you could have a Combobox with multiple selections. If that's the case, the only time the values will be available in the .ItemsSelected property is while the Combobox has focus and is dropped down.

A way to work around this is to assign the Combobox's .Value property to an array. The array will contain the selected values. You may count them by taking the upper bound of the array and adding 1:

Dim comboitems() as Variant
Dim count as Long

comboitems = yourcombobox.Value

' array is 0-based so add one to get the count
count = UBound(comboitems) + 1

If the array is multidimensional, you read the value this way:

' array is 0-based so add one to get the count
count = UBound(comboitems, [dimension]) + 1

' where [dimension] is a 1-based index equivalent to the 'column' of the data

I hope that helps!

*Note: multivalued fields are usually ill-advised, as they are poorly supported by Access and usually mean you should be normalizing your tables, i.e. breaking out the multivalued field into another table.

Solution 2

To count the options, it is:

Me!<ComboBox_name>.ListCount

or to be precise, in case you use column headings:

Me!<ComboBox_name>.ListCount - Abs(Me!<ComboBox_name>.ColumnHeads)

Comments

[Necromancer]

Thanks :-) You Saved me.