Adding an item to the end of a multi-column listbox

Question

Being a beginner in this, I've tried to search for a solution to my issue. I created a form in Access 2013 with a List Box. The List Box is bounded to a table with 10 fields.

For the List Box, in the Property Sheet, I've set the Column Count to 10, but have hidden some columns, i.e. some has a Column Widths of 0" because I'm only showing important data in the List Box.

The Row Source Type of the List Box is set to Table/Query.

When I open the form, it queries the table and populates the List Box with all data.

I have 4 unbounded Text Box that allows the user to enter data, and have a button to allow the input data to be added to the end of the List Box and also be automatically added to the table.

I've searched for how to do this:

I've tried to use the .List property of the List Box, but it does not exist.

I've tried to do something like:

Me.MyList.AddItem "" & ";" & Me.textBoxValueOne & ";" & Me.testBoxValueTwo & ";" & ""

However, VBA complains I need to change the .RowSourceType to Value List, and when I added it, the List Box clears all the data, and adds this data as the first entry, which is not what I want.

I've added the following code, which adds a blank entry to the end of the List Box as well as table, but I don't know where to go further with it to save the input data from the Text Box to the end of the List Box and table:

DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSaveRecord
Me.MyList.Requery

How can I achieve this?

Thanks.

score 0 · Accepted Answer · answered Feb 16 '18 at 23:18

You need to add the record to the table first and then requery your listbox.

After adding the new record try

Me![column name] = textbox1.value
And other textboxes
Followed by your save command.

Once you've added the record. Simply requery the listbox.

Me.listbox.requery

Adding an item to the end of a multi-column listbox

1 Answers1