Multi column listbox to add values to multiple fields in a table

[Nothnless]

What I need to happen is for the user to make selections from a list box (multiple) that contains 3 columns and have a tables values filled in with the users selection. This is easily done 1:1 with a single column list box bound to a single field. But I need all 3 colmuns from the listbox selection to fill in the values for 3 fields in my table. There seems to be no way to do this and so I’m confused on why would Access allow multiple columns to be shown in a listbox if you can only make use of the first column?

So here is what I have setup:
I have a blank table (BlankTable) with 3 columns [First] [Last] [Age]
I have a table with values (ListBoxTable) with 3 columns [First] [Last] [Age]

I have a user form (Form1) with a multiselect listbox (FirstList) that has a Control Source BlankTable.First and Row Source ListBoxTable.First

Basically I want the Control Source to be BlankTable.First & BlankTable.Last & BlankTable.Age Obviously I cant do this so I created 3 listboxes, one for each field and bound to their respective fields. I added some VBA to say if the user selects a value in listbox1 then auto select the same values in listbox2 and listbox3 (this all works) but when I go to the BlankTable, the fields are blank. So then I found out that you cant use VBA to fill in table values programmatically from a forms control.

I just need someway for the user to make a selection from a listbox that will fill in multiple fields in a table, is this possible?

 

[Micron]

It sounds like a bad idea to me, given that you already would have this data in some other table. The exception might be if your list items come from a value list.
These things you say "obviously" can't be done is interesting but wrong IMO.

If you must append list column data to a table, try using the listbox.Column property. The column collection is 0 based, so the first column is 0, regardless of whether or not you can see it. Not sure if you can use a query and refer to listbox columns to retrieve values if that's what you need. It is possible with combos but it's a bit tricky. In the end, you might need to use code because I'm fairly certain a query cannot loop over multiple list items.
BTW, you should not use reserved names for any object. That would include First and Last.

 

[Nothnless]

It sounds like a bad idea to me, given that you already would have this data in some other table. The exception might be if your list items come from a value list.
These things you say "obviously" can't be done is interesting but wrong IMO.

If you must append list column data to a table, try using the listbox.Column property. The column collection is 0 based, so the first column is 0, regardless of whether or not you can see it. Not sure if you can use a query and refer to listbox columns to retrieve values if that's what you need. It is possible with combos but it's a bit tricky. In the end, you might need to use code because I'm fairly certain a query cannot loop over multiple list items.
BTW, you should not use reserved names for any object. That would include First and Last.

Wrong? Then tell me how so because that's the question. Is there a way for a single multi select listbox to be bound to 3 different fields on a table?

I'm only using the other table to use for the dropdown list. It is nothing but a value list contained as a table because I think its simpler to update. So lets for get about it as a table then and say I have a listbox with a value list that contains 3 columns. When the user selects an options all 3 columns should fill in the table fields not just the 1st column.

 

[Micron]

A listbox does not have a dropdown list, so what are we talking about exactly? I'm quite a literal person (sometimes it's a good thing, sometimes not) and I'm afraid your terminology is not helping me understand. Also, I did say that IF your list rows came from a value list then writing to a table could be OK. A value list isn't and cannot be a table but as you said, we can forget about that. So you would want each column of a listbox to be written to a table? You didn't cover the issue I raised about the listbox being multi select.

Maybe there's a way you can make a file (or some variation of you file) available?

 

[Nothnless]

A listbox does not have a dropdown list, so what are we talking about exactly? I'm quite a literal person (sometimes it's a good thing, sometimes not) and I'm afraid your terminology is not helping me understand. Also, I did say that IF your list rows came from a value list then writing to a table could be OK. A value list isn't and cannot be a table but as you said, we can forget about that. So you would want each column of a listbox to be written to a table? You didn't cover the issue I raised about the listbox being multi select.

Maybe there's a way you can make a file (or some variation of you file) available?

Thats a good idea I will make a quick file. You're right I'm just referring to a list when I say dropdown so what I just mean is a multi select listbox.

 

[Micron]

OK, will wait for your file.

what I just mean is a multi select listbox.

Multi select definitely means vba code is required.

 

[Nothnless]

Here is the link.

New Microsoft Access Database.accdb

drive.google.com

 

[Micron]

OK, you not only want to get at each column of a listbox, you want to concatenate values from each list item row from each column. That's a different matter altogether. At least that's how I interpret the caption above the form image?

 

[Nothnless]

OK, you not only want to get at each column of a listbox, you want to concatenate values from each list item row from each column. That's a different matter altogether. At least that's how I interpret the caption above the form image?

Yes, I wasn't aware it was a different matter because the listbox concatenates on its own. Allow you have to do is make your selections and the listbox will concatenate to the bound field.

I dont understand why Access allows you to create a multi column listbox if you cant do anything with the other columns.

 

[Micron]

I've made a bunch of changes that seem to work but probably can't upload the db to where I got it from?