CODEWITHSUNDEEP
ms-accessvba
Joe
Joe

Reputation: 7004

Access VBA - adding row with multiple columns

I'm trying to add a multiple column item to a ListBox in access using VBA. Can't for the life of me work out how to do it though. There seems to be multiple ways on the internet, none of them have worked for me.

Here's an example:

Sub AddMultipleColumn()
'Add multiple Columns to a listbox
ListBox1.Clear 'Make sure the Listbox is empty
ListBox1.ColumnCount = 3 'Set the column Amount
'Fill the Listbox
ListBox1.AddItem "Row Number 1" 'Additem creates a new row
ListBox1.List(0, 1) = "Column Number 2" 'List(x,y) X is the row number, Y the column number
ListBox1.List(0, 2) = "Column Number 3"
ListBox1.List(0, 3) = "Column Number 4"
End Sub

http://visiblevisual.com/jupgrade/index.php/general-net/77-multiple-columns

Here's my code (already configured to have three columns):

lst_TemplateEditorList.AddItem "1"
lst_TemplateEditorList.List(0, 0) = "1"
lst_TemplateEditorList.List(0, 1) = "2"
lst_TemplateEditorList.List(0, 2) = "3"

But I get the compile error "Method or data member not found" on "lst_TemplateEditorList.List" because there's no List method/property.

I found something that used "Column" instead of "List", but that didn't work, and a few have no argument with "AddItem" which causes an error too:

lst_TemplateEditorList.AddItem
lst_TemplateEditorList.Column(0, 0) = "1"
lst_TemplateEditorList.Column(0, 1) = "2"
lst_TemplateEditorList.Column(0, 2) = "3"

But most seem to suggest the first should work, eg:

Adding items in a Listbox with multiple columns

vba listbox multicolumn add

It seems like it should be so simple... but I'm stumped. Can anyone tell me what I'm doing wrong?

Thanks

Upvotes: 1

Views: 3714

Answers (1)

Andre
Andre

Reputation: 27644

Listboxes in Access have a different set of properties/methods than listboxes in other Office programs (there they belong to the "Microsoft Forms" object model)

See https://msdn.microsoft.com/en-us/library/office/ff195480.aspx

In Access you simply add the multi-column data like you would in the RowSource property for RowSourceType = Value List, separated by semicolons.

With Me.lst_TemplateEditorList
    .AddItem "1;2;3"   ' first row
    .AddItem "4;5;6"   ' second row
End With

Edit

The Access 2010 Developer Help adds to the confusion: if you search for List, you get the Forms-Listbox and its properties, including .List. You have to look very closely to notice that you have left the Access realm and have entered the Microsoft Forms realm. German Access here, but you get the idea.

enter image description here

Upvotes: 1

Related Questions