Search code examples
arraysvbams-accesscombobox

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.

Error Combo box general properties Data properties

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

    or

    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

    See also: ComboBox.RowSourceType property (Access)