Listbox - Add and retrieve Multiple items

[AlunR]

I have a listbox which is populated from a table. I want to be able to save multiple selections to a master table.

I cannot get anywhere with this at the moment - please help!

 

[Isadore]

loser: The other day I built a small form that does the following:

From a listbox you make multiple selections. In one routine the selections are concatenated horizontally, then, via a hidden listbox, placed in seprarate textboxes. In the other routine, the multiple selection is first placed in multiple textboxes and then concatenated into a single textbox. If you email me at biochem3d@yahoo.com I'll send it to you. The code, which you might be able to parse here, is as follows:

Option Compare Database
Option Explicit
Dim strItems, strItem As String
Dim intCurrentRow As Integer
Dim i, intMyIndex As Integer
Dim strMyID As String
Dim ctlSource As Control
Dim ctlDest As Control
Dim lngID As Long

Private Sub cmdClear_Click()
[CC1] = Null
[CC1].Enabled = False
[CC2] = Null
[CC2].Enabled = False
[CC3] = Null
[CC3].Enabled = False
[CC4] = Null
[CC4].Enabled = False
[CC5] = Null
[CC5].Enabled = False
[C1] = Null
[C1].Enabled = False
[C2] = Null
[C2].Enabled = False
[C3] = Null
[C3].Enabled = False
[C4] = Null
[C4].Enabled = False
[C5] = Null
[C5].Enabled = False
[cmdPop].Enabled = False
[cmdPop2].Enabled = False
[cmdConc].Enabled = False
[cmdConc2].Enabled = False
[Concat] = Null
[Concat].Enabled = False
[Concat2] = Null
[Concat2].Enabled = False
'clear the rowsource
[lstClients].RowSource = ""
'repopulate the listbox
[lstClients].RowSource = "qyNames"
End Sub

Private Sub cmdConc_Click()
'the second Pop cmd button is now functional
If [cmdPop2].Enabled = False Then
[cmdPop2].Enabled = True
End If
If [Concat].Enabled = False Then
[Concat].Enabled = True
End If
'Lena keep in mind there are many variations and themes
'especially here - you could study the code and actually
'and probably come up with a slicker way to do it
'this code works, so its a start - here what Id do
'go find each selected item, concatenate it
'notice you can return to the listbox and pick more clients
'clear variables
strItems = ""
i = 0
Set ctlSource = Forms![frmClients]![lstClients]
Set ctlDest = Forms![frmClients]![Concat]
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
i = i + 1
If i = 1 Then
strItems = ctlSource.Column(0, intCurrentRow)
Else
strItems = strItems & ", " & ctlSource.Column(0, intCurrentRow)
End If
End If
Next intCurrentRow
[Concat] = strItems

End Sub

Private Sub cmdConc2_Click()
If [Concat2].Enabled = False Then
[Concat2].Enabled = True
End If
'just concatenate and populate textbox
'easiest one to do
i = 0
strItems = ""
For i = 1 To 5
If i = 1 Then
'make sure there's something there
If Not IsNull([C1]) Then
strItems = [C1]
End If
ElseIf i = 2 Then
If Not IsNull([C2]) Then
strItems = strItems & ", " & [C2]
End If
ElseIf i = 3 Then
If Not IsNull([C2]) Then
strItems = strItems & ", " & [C3]
End If
ElseIf i = 4 Then
If Not IsNull([C2]) Then
strItems = strItems & ", " & [C4]
End If
ElseIf i = 5 Then
If Not IsNull([C2]) Then
strItems = strItems & ", " & [C5]
End If
End If
Next i
'now populate
[Concat2] = strItems
End Sub

Private Sub cmdPop_Click()
i = 0
'second concat button now functional
If [cmdConc2].Enabled = False Then
[cmdConc2].Enabled = True
End If
If [C1].Enabled = False Then
[C1].Enabled = True
[C2].Enabled = True
[C3].Enabled = True
[C4].Enabled = True
[C5].Enabled = True
End If
'now we just loop through the listbox and capture all the selected
'names and populate the textboxes along the way
i = 0
Set ctlSource = Forms![frmClients]![lstClients]
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
i = i + 1
If i = 1 Then
[C1] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 2 Then
[C2] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 3 Then
[C3] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 4 Then
[C4] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 5 Then
[C5] = ctlSource.Column(0, intCurrentRow)
End If
End If
Next intCurrentRow
End Sub

Private Sub cmdPop2_Click()
'the second layer of client boxes can now be turned on and populated
If [CC1].Enabled = False Then
[CC1].Enabled = True
[CC2].Enabled = True
[CC3].Enabled = True
[CC4].Enabled = True
[CC5].Enabled = True
End If
'now Lena, here is where you could develop another route
'this is what some people would call a workaround
'what we do here is transfer the names to a listbox
'which has this nice property of recognizing commas
'as text deliminiters (this property is derived by
'setting Row Source Type to Value List. This can be a
'drawback though if your text fields have commas in
'which case this workaround wouldnt work - so we transfer
'the string of names to a hidden listbox, where it will stack
'them individually and we can then loop through like we
'did earlier and populate the textboxes...
[lstClients2].RowSource = [Concat]
i = 0
Set ctlSource = Forms![frmClients]![lstClients2]
For intCurrentRow = 0 To ctlSource.ListCount - 1
i = i + 1
If i = 1 Then
[CC1] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 2 Then
[CC2] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 3 Then
[CC3] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 4 Then
[CC4] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 5 Then
[CC5] = ctlSource.Column(0, intCurrentRow)
End If
Next intCurrentRow
End Sub

Private Sub lstClients_Click()
'if the concat and client textboxes are disenabled
'then turn them on (a selection has been made)
'and turn on the buttons since they are now functional
If [Concat].Enabled = False Then
[cmdPop].Enabled = True
[cmdConc].Enabled = True
End If
End Sub