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

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?

CodePudding user response：

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