Record multiple values from two multiselect listboxes in a table 1

[MrMode]

I have two multiselect listboxes.

I want the user to be able to make multiple selections in ListA (ListPerson) and multiple selections in ListB (Choice) then have these selections written to a table.

For example:

ListPerson
John
Jane
Bill

ListChoice
Choice 1
Choice 2
Choice 3

If John and Jane both have Choice 1 and Choice 2 selected then I would want the following records in the table

John Choice 1
John Choice 2
Jane Choice 1
Jane Choice 2

I am guessing this is some kind of nested for each loop but I have no clue where to start or how to achieve it?!

Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant


Set db = CurrentDb()
Set Rs = db.OpenRecordset("tblChoicesMade", dbOpenDynaset, dbAppendOnly)

Set ctl = Me.ListPerson
For Each varItem In ctl.ItemsSelected
'Assuming I need some additional loop element here to cycle through the second multiselect listbox Me.ListChoice
Rs.AddNew
Rs!PersonID = ctl.Column(3, varItem)
Rs!ChoiceID = ctl.Column(3, varItem)
Rs.Update
Next varItem

 

[dhookom]

I assume you have tables of unique persons and unique choices. I would attempt to do this with a query. There are some code snippets at the bottom of Access Queries FAQ


Duane
Hook'D on Access
MS Access MVP

 

[MajP]

loop the itemsselected collection and do an insert query

Private Sub Command3_Click()
  Dim lstOne As Access.ListBox
  Dim lstTwo As Access.ListBox
  Dim strSql As String
  Dim Person As String
  Dim choice As String
  Dim itm1 As Variant
  Dim itm2 As Variant
  Set lstOne = Me.List0
  Set lstTwo = Me.List2
  
  For Each itm1 In lstOne.ItemsSelected
    For Each itm2 In lstTwo.ItemsSelected
      'You could use the column property here or simply make the desired column the bound column
      Person = lstOne.ItemData(itm1)
      choice = lstTwo.ItemData(itm2)
      Person = "'" & Person & "'"
      choice = "'" & choice & "'"
      strSql = "INSERT INTO tblData (Person, Choice) VALUES (" & Person & ", " & choice & ")"
      'Debug.Print strSql
      CurrentDb.Execute strSql
    Next itm2
  Next itm1
End Sub

 

[MrMode]

Awesome MajP Thank you! And thanks for your suggestion Duane

One quick question as I am really punchy now, how do use the column property:

'You could use the column property here or simply make the desired column the bound column
      Person = lstOne.ItemData(itm1)

The data I want is in the 4th column in the Person listbox, but my code is not working...

      'You could use the column property here or simply make the desired column the bound column
      Person= lstOne.ItemData(3, itm1)

Sorry to be a pain

 

[MajP]

the itemsselected property returns a collection of variants. These variants are the indices of the rows selected.

If you have a listbox and you want to get a value, you can use the column property where you supply a row and column value. So column property is kind of a dumb name. Or if you just want the value from the bound column you can use the itemdata property and supply just a row number.
so
somelistbox.column(rowNumber, columnNumber)
or for the bound column simply
somelistbox.itemdata(rownumber)

So
Person = lstOne.Column(3, itm1)
Or if column 3 is the bound column then simply
Person = lstOne.itemdata(itm1)

Rember these indices are 0 based. 1 row/column is 0.

 

[MrMode]

Panic over, got it

Person= lstOne.Column(3, itm1)

Sorry, was being slow.

Thanks again

 

[MajP]

BTW you could use DAO or ADO to do this like you were showing. SQL is simpler and more efficient if you can figure out hot to concatenate together the insert string.

 

[MrMode]

Thanks, i will bear that in mind, much easier to work with sql