MS Access 2016 - Multi column listbox to add values to multiple fields in a table

Question

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?

Does this answer your question? [Retrieve column values of the selected row of a multicolumn Access listbox](https://stackoverflow.com/questions/4649000/retrieve-column-values-of-the-selected-row-of-a-multicolumn-access-listbox) — Olivier Jacot-Descombes, Sep 24 '22 at 16:08
Why are you duplicating data between tables? Why not just save table1 ID into table2? Exactly what are you trying to accomplish with this process? Certainly can programmatically save values from form control into table. Edit question to show attempted code. — June7, Sep 24 '22 at 16:24
@June7 I am duplicating data between tables because [ListBoxTable] is just used to populate the listbox dropdown in my form. [BlankTable] is always blank until the user starts filling it in with all the combo and list boxes I have on the form. [BlankTable] is then read by another application and then deleted. Essentially I'm using Access as kind a an IDE for something else. As far as the code, I'll post it but as I said it works as coded but the values are not added to the table. The code does not do this and its not supposed to. The bound list box is suppose to. — Deon Bowers, Sep 24 '22 at 16:38
@OlivierJacot-Descombes No, I can retrieve all the column values for my listbox just fine. What I need to do is use those values. So I need to insert all of the values in each subsequent column into my table. — Deon Bowers, Sep 24 '22 at 16:44
A control can be bound to only one field so only one value can automatically feed to table. Saving the related info to other fields requires code (macro or VBA). If form is bound to table, make sure on NewRecord row then in listbox AfterUpdate event: `Me!Age = Me.Listbox.Column(2)`. If listbox is set for multi-select then code is required for all and is more complicated. — June7, Sep 24 '22 at 17:05
@June7 Okay thanks, after 8 hours I came to the same conclusion but I wanted to post to make sure. Yes it needs to be a multiselect list box so I'll have to write some nested for each loops which is what I was hoping to avoid. It makes updating new values more difficult. It's frustrating because it just seems so intuitive that if you have a multi column listbox or combobox the one might want to bind more than just the first column. — Deon Bowers, Sep 24 '22 at 17:45
Correction, multi-select listbox can be bound to multi-value field but if you are wanting to save each selection to a record, that will require UNBOUND listbox and looping code. A common topic. — June7, Sep 24 '22 at 17:48
Have you considered just saving the primary key in the table, and then using a query that includes all fields as the basis for the further processing? — Applecore, Sep 24 '22 at 18:06
How does this other app read from table? Can it read from a query? — June7, Sep 24 '22 at 18:16
@June7 The user is building the query that the other app reads from by making their selections on the form. I'm just using the BlankTable to save the users selections. I'm saving the selections to a table so that other users and reuse queries built by other users. This is quite simple and easily done if I just bind multi select Listboxs to multiselect fields in my table. However, instead of have 3 listboxes to bind to 3 fields I'm trying to have 1 listbox with 3 columns that will add the values to 3 fields in my table. — Deon Bowers, Sep 24 '22 at 18:28
If other app can read a query, then no need to save related info to table, just the ID. That was at root of my original question of why you are duplicating data between tables. Yes, 3 listboxes makes no sense - users should not have to select related info which just injects possibility of errors into the process. — June7, Sep 24 '22 at 18:45
@June7 The query has to be BUILT. The user builds the query based on their selections. The table IS the query because it's what contains the users selections. I use the selections to create a passthrough query. Unless you are saying that the users listbox selections can just be added to a query instead of a table but so what! How does That address the listbox issue? Are you saying the a query can take in all the columns from a multi select listbox so that negates the need for a table? — Deon Bowers, Sep 24 '22 at 19:27
I am confused. Are users selecting what fields they want used in output? Or are they selecting records? Applecore suggestion is to save ID, that is selecting records. You said you would use that approach. Save ID to 'temp' table, join to original table to pull related fields. Nothing extraordinary. Query design is static, records retrieved is dynamic. — June7, Sep 24 '22 at 19:43
Selecting Records. In the passthrough query [First] would be the filtering criteria so like WHERE [First] = Deon. [Last] is more like the table it's on. So like FROM table Bowers. So someone could select both Deon and John from the ListBox but John might be on another table which is why my VBA needs to see all three columns from the listbox to create a passthrough query with the appropriate joins that maybe needed. Just being able to record the 2nd & 3rd listbox columns would solve this without need to write the loop code to make updating easier. — Deon Bowers, Sep 24 '22 at 20:14
Gets more confusing. Why would source data (Deon and John) be from different tables? What is your data schema? With multi-select listbox, can't avoid looping code if you want to save each selection to a record, whether or not pulling values from additional columns. — June7, Sep 24 '22 at 22:06
Ok, just wanted to make sure I had to code it. As far as the schema and all, I didn't create it and there is like 100 tables which is why I'm creating an IDE in the first place. — Deon Bowers, Sep 25 '22 at 01:46
If you need a reference for looping listbox selected items, https://stackoverflow.com/questions/59975642/how-to-loop-through-and-capture-each-selected-item-in-an-access-list-box — June7, Sep 25 '22 at 16:29

mazoula · Answer 1 · 2022-09-26T05:17:43.293

The first comment was the answer, but after all those comments I will write out a more explicit answer. it seems to me the Access designers didn't include much syntactic sugar when there was a relatively quick way to do something. They also didn't include Upserts, A RowID function, or the Over statement, among other things and I've spent many, many hours as a result.

You can do what you want in the same way you make a form for rapid data entry of which and per the comments there are many examples on stack overflow. The key is the FirstList must be unbound .

First I created the tables with some sample data then I used the form wizard the create a form based on BlankTable.

-------------------------------------------------------------------------------------
|         ID         |       First        |        Last        |        Age         |
-------------------------------------------------------------------------------------
|                  1 | James              | Kirk               |                 30 |
-------------------------------------------------------------------------------------
|                  2 | Leonard            | Mcoy               |                 30 |
-------------------------------------------------------------------------------------
|                  3 | Spock              | Spock              |                 25 |
-------------------------------------------------------------------------------------
|                  4 | Bengamin           | Sisko              |                 35 |
-------------------------------------------------------------------------------------
|                  5 | John               | Picard             |                 40 |
-------------------------------------------------------------------------------------

Then you put the unbound FirstList and a button in the header or wherever. Copies is just there for completeness and the only reason for a loop in the sql. If you did want to hurry things along.

Here is the code for the button:

Private Sub cmdInsertRecord_Click()
'There are many ways to insert records, but I feel this is the most declaritive and hence self documenting
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("BlankTable")
Dim i As Integer
For i = 0 To Me.txtCopies 'loop'
rs.AddNew
rs!First = Me.FirstList.Column(1) 'id column is 0 and hidden'
rs!Last = Me.FirstList.Column(2)
rs!Age = Me.FirstList.Column(3)
rs.Update
Next
Me.Requery
'clean up
Set rs = Nothing
End Sub
'per the comment it is a short step to replace Adding a new record to just running the query that would run on blank table

edit: and here is the multi-select listbox version. copies is omitted because it is unclear how it should be implemented in this case.

Private Sub cmdInsertRecord_Click()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("BlankTable")
Dim i As Integer
For i = 0 To Me.FirstList.ListCount - 1
If Me.FirstList.Selected(i) = True Then
rs.AddNew
rs!First = Me.FirstList.Column(1, i)
rs!Last = Me.FirstList.Column(2, i)
rs!Age = Me.FirstList.Column(3, i)
rs.Update
End If
Next i
Me.Requery
Set rs = Nothing
End Sub

Your code does not save records for multiple items selected in listbox and therefore does not fully answer the question. For that, need to use listbox ItemsSelected property https://stackoverflow.com/questions/59975642/how-to-loop-through-and-capture-each-selected-item-in-an-access-list-box — June7, Sep 25 '22 at 16:26
Here is a link to a sample file to clarify https://drive.google.com/file/d/17-kjxlw9-Nex_RN9889OQcQLDmRoJobk/view?usp=sharing . Using example from mazoula if I select James & Leonard then [First] of row 1 should contain James, Leonard [Last] Kirk, Mcoy [Age] 30,30. As I've stated, I'm doing it this way because I need to save the users selections in this format for my passthrough query. I do appreciate the efforts. — Deon Bowers, Sep 26 '22 at 14:21
I see that mazoula made an edit I will test and see if this works. Thank you! — Deon Bowers, Sep 26 '22 at 14:27

Deon Bowers · Accepted Answer · 2022-09-27T21:03:57.683

Here is how it would be done:

Dim i As Integer
Dim varItem As Variant
Dim sql As String, str1 As String, str2 As String, str3 As String

If Me.List9.ItemsSelected.Count = 0 Then
     MsgBox "You must first select 1 or more list items"
     Exit Sub
End If

If MsgBox("Edit Selected Record?", vbYesNo) = vbNo Then Exit Sub

i = 1
For Each varItem In Me.List9.ItemsSelected
     str1 = str1 & Me.List9.Column(0, varItem) & ", "
     str2 = str2 & Me.List9.Column(1, varItem) & ", "
     str3 = str3 & Me.List9.Column(2, varItem) & ", "
     i = i + 1
Next
str1 = Left(str1, Len(str1) - 2)
str2 = Left(str2, Len(str2) - 2)
str3 = Left(str3, Len(str3) - 2)
sql = "UPDATE A_SQL_Reference_tbl SET FriendlyListName='" & str1 & "', FieldName='" & str2 & "', FieldTable='" & str3 & "'"
sql = sql & " WHERE A_SQL_Reference_tbl.ID=" & Me.sfcList.Form.ID
CurrentDb.Execute sql, dbFailOnError
Me.Requery

MS Access 2016 - Multi column listbox to add values to multiple fields in a table

2 Answers2

Linked