Multiple List Box.

[Luther1978]

Now that I've seen on the MS Samples DB that a Multiple Selection list box is available. I want one. I think it would solve many of my problems.

I've investigated the Developer Solution DB and have drawn a blank further than setting the Properties-Other-Multi Select to Simple/Extended.
This does nothing except turn a listbox that works for one a single selection into a listbox that does nothing. Obviously you can't store multiple values into a field, unless the field was an array. But surely there is someway of storing the multiple values in a table.

If its of any Help here is an outline Of

ParentForm has FormID 01

ListBox has values
1
2
3
4
5

If I were to multiple Select values, 2 & 4 from the listbox, the JunctionTable would read.

Junctiontable
01 2
01 4

????

Help Please

 

[FancyPrairie]

Not sure what exactly you want, but here's a start. It loops thru the items selected in a list box and adds the results to the table (assuming column 0 of the list box contains the value you want to store in the table)

Dim cnn as ADODB.Connection
Dim rst as ADODB.Recordset
Dim var As Variant

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open "Select * from YourTable;",cnn,,adOpenKeyset,adLockOptimistic

For Each var In List0.ItemsSelected
msgbox List0.Column(0, var)
rst.AddNew
rst!YourFieldName = List0.Column(0,var)
rst.Update
Next

rst.close
set rst = nothing

 

[HFloyd]

Hi Luther,

In order to make a multi-select list box useful, you need to write some VBA coding to do something with it. What it looks like you need to do is to run code to add new records to your "JunctionTable." The easiest way to do this is to have a command button the user clicks after selecting the values, and have the code in the OnClick event for the command button.

Here is some slightly modified code from a project I did, you will need to edit it for your purposes, but hopefully it will give you an idea. (I have left out error handling for clarity)

Sub cmdButton_OnClick

' Set up variables and return Control object variable pointing to list box.
Dim ctlList As Control, varItem As Variant
Set ctlList = Me![lstListBox]

'Check that at least one item has been selected
If ctlList.ItemsSelected.Count < 1 Then
    MsgBox &quot;Please Select at least one item!&quot;, vbOKOnly, &quot;Select Item!&quot;
    Exit Sub
End If

'Set up variables and initialize beginning and end of SQL strings.
Dim SQL1 As String, SQL2 As String, strSQL As String
SQL1 = &quot;INSERT INTO JunctionTable ( FormID, ItemID ) SELECT tblUsedForParentForm.FormID, &quot;
SQL2 = &quot;FROM  tblUsedForParentForm WHERE (((tblUsedForParentForm.FormID)=[Forms]![frmParentForm]![FormID]));&quot;

' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected 
        ' Add value to strSQL, put together SQL
        strSQL = ctlList.ItemData(varItem) & &quot; AS ItemID&quot;
	strSQL = SQL1 & strSQL & SQL2 
	'Run SQL statement to add records
	DoCmd.SetWarnings False
	DoCmd.RunSQL strSQL
	DoCmd.SetWarnings True
	MsgBox &quot;Success! Records Added. &quot;, vbOKOnly, &quot;Success!&quot;
    Next varItem

End Sub

Good luck,

Heather

http://www.floydinnovations.com

 

[Luther1978]

I see you've answered my question as if I were using ASP. As much as I would love to be using ASP for my DB, I'm left with the constrictions of Access.

I can put this code together in Access but where would I put it? On AfterUpdate of the Form holding my List, or On AFterUpdate on My list?

Many thanks for your advice,

 

[Luther1978]

Thanks Guys, You have both been very helpful, and I think that I can benefit from your code.

Cheers

Luther