How to fix MS Access Form Combobox error in VBA

I originally had my combobox cbSortOrder defined in the form as a fixed Value List. I am now attempting to set this in VBA code, using an array. I have tried with a variant and string array but do not think my issue is connected with that but is something to do with the combobox definition.

When attempting to set these values in a With statement, I get the Object Doesn't Support Method error.

I have attached screenprints of the actual error and properties of my combobox.

Solution

Unfortunately, you cannot use collections as row source in Access. A Row Source Type of Value List refers to a comma (or semicolon) separated text (depends on the list separator setting in Windows).

If you don't want to use such a value list, use a Row Source Type of Table/Query and specify a table or query name. You can also insert a SELECT statement directly into this field.

This also means, that you must store the entries in a table for this Row Source Type. You can also use a local table instead of a table in the back-end.

Also, the ComboBox has no List property. Use the RowSource property instead. In the code behind of the form you can omit the Forms("formName") part.

With cbSortOrder
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT Id, Description FROM tlkpSortOrder ORDER BY Description"
End With

With cbSortOrder
    .ColumnCount = 2    ' If you have and id and a text
    .ColumnWidths = "0" ' Hides the Id column
    .RowSourceType = "Value List"
    .RowSource = "1,Ascending,2,Descending,3,Undefined"
End With