Using Simple Multi-Select Listbox

  • Thread starter Thread starter Reda
  • Start date Start date
R

Reda

How can I store in a table the data for each row selected
using Simple Multi-Select Listbox property?

Thanks
 
Why do you want to do this .. this violate the First Normal Form (1NF
???
You should not store multiple pieces of data in a single field.

RD
 
Reda said:
How can I store in a table the data for each row selected
using Simple Multi-Select Listbox property?
Click to expand...

You don't (or at least shouldn't). A field in a table should contain no
more than exactly one piece of data. If you need to *relate* multiple
values to a single row, then you need to add another *related* table with a
one-many-*relationship* from the original to the new. Then you can use a
sub-form to add as many choices as you want and still have a properly
designed *relational* database.
 
How can I store in a table the data for each row selected
using Simple Multi-Select Listbox property?
Click to expand...

The best way is to not use that approach at all as it is poor database design
and can be difficult to implement. Instead, use a child table and form to store
the individual values and a combo or list box to enter the one value per record.

That having been said, if you must move forward with this approach, you can
retrieve the individual selected values and concatenate them into a single
string to store in a field, using the following approach:

Copy this function to a standard module (global module, not a module behind a
form or report):

'****FUNCTION START
Public Function fncBuildListFromMSelectListbox(ctlListBox As Control, _
Optional strDelimiter As String = ",") As String
' Comments : Builds a CSV list from selections
' in multi-select listbox
' Parameters: ctlListBox - a reference to the listbox control
' strDelimiter - character to use as delimiter
' Default delimiter is ","
' Returns : String - the "list"
' Created : 11/12/03 14:15 Bruce M. Thompson
' Modified :
'
' --------------------------------------------------

On Error GoTo fncBuildListFromMSelectListbox_ERR

Dim varItem As Variant
Dim strList As String

'Build the string by obtaining the value of each
'selected row and concatenating
For Each varItem In ctlListBox.ItemsSelected
strList = strList + ctlListBox.Column(0, varItem) & strDelimiter
Next

'Remove extra delimiter from end of string
If Right(strList, 1) = strDelimiter Then _
strList = Left(strList, Len(strList) - 1)

'Assign list as function's return value
fncBuildListFromMSelectListbox = strList

fncBuildListFromMSelectListbox_EXIT:
Exit Function

fncBuildListFromMSelectListbox_ERR:
MsgBox "Error " & Err.Number & _
" occurred in fncBuildListFromMSelectListbox: " _
& Err.Description
Resume fncBuildListFromMSelectListbox_EXIT

End Function
'****FUNCTION END

You can assign the value to your field using the following syntax (you can use
the listbox's "OnExit" event procedure):

Me.txtValueList = fncBuildListFromMSelectListbox(Me.ListBox1)

You will need to parse the values from the field in order to use them (such as
reselecting the items in the listbox in the "OnCurrent" event to display the
values).

Now, you might, again, want to reconsider the child table approach. <g>
 
Hi,



if you meant to insert a line per selected value:



Dim db As Database : Set db=CurrentDb
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox


For Each varItem In ctl.ItemsSelected
db.Execute "INSERT INTO myTable( fieldNameHere) VALUES(""" & _
ctl.ItemData(varItem) & """) ; "
Next varItem




Hoping i tmay help,
Vanderghast, Access MVP
 
Back
Top