Multiselect List Box-Access 2002

[Q]

I am trying to create a multiselect list box in a form. I
have made a list box listing the 5-6 choices and have
added an inidivual add and remove button, as well as
another list for the selected choices to go into. I am
able to select the items and move them to the new list
box. The problem I am having is that once I have selected
the items and saved the form, they do not go into the
field of table desired. The field is blank. What is
needed to save the selections to the field?

Below is the code I have so far for the event procedure.

Private Sub Add_Button_Click_Click()
Dim VaxListCounter As Integer, VaxCurrentCounter As
Integer
Dim VaxListItems As Integer, VaxCurrentItems As
Integer
Dim ListStr As String, FoundInList As Integer
VaxListItems =

• .ListCount - 1
  VaxCurrentItems = [Cap].ListCount - 1
  For VaxListCounter = 0 To VaxListItems
  If
  • .Selected(VaxListCounter) = True Then
    If IsNull([Cap].RowSource) Then
    ListStr =
    • .Column(0,
      VaxListCounter) & ";"
      [Cap].RowSource = ListStr
      Else
      FoundInList = False
      For VaxCurrentCounter = 0 To
      VaxCurrentItems
      If [Cap].Column(0, VaxCurrentCounter)
      =
      • .Column(0, VaxListCounter) Then
        FoundInList = True
        End If
        Next VaxCurrentCounter
        If Not FoundInList Then
        ListStr = [Cap].RowSource &
        • .Column(0, VaxListCounter) & ";"
          [Cap].RowSource = ""
          [Cap].RowSource = ListStr
          End If
          End If
          End If
          Next VaxListCounter
          
          End Sub
          
          Private Sub Remove_Button_Click_Click()
          Dim ListStr As String
          Dim VaxCurrentItems As Integer, VaxCurrentCounter As
          Integer
          Dim VaxListItems As Integer
          VaxListItems = [Cap].ListCount - 1
          ListStr = ""
          For VaxCurrentCounter = 0 To VaxListItems
          If [Cap].Selected(VaxCurrentCounter) = False Then
          ListStr = ListStr & [Cap].Column(0,
          VaxCurrentCounter) & ";"
          End If
          Next VaxCurrentCounter
          [Cap].RowSource = ""
          [Cap].RowSource = ListStr
          
          End Sub

 

[Douglas J. Steele]

How are you trying to put them into a table field? You can't bind a
multiselect list box: it's a violation of relational database theory to
attempt to store more than one value in a single field, so Access doesn't
support it.

 

[Q]

I was trying to assign the list box to the desired field
via the "control source" field in properties. I would
like a person to select as many values as possible and
then have them listed in a field of a table. (i.e. table
field would list such selected choices as "warehouse,
distribution center, contract warehouse". If I am not
able to have more than one value in a single field, what
other options are available. Oh and thanks for the reply
Doug.

Quinton

-----Original Message-----
How are you trying to put them into a table field? You can't bind a
multiselect list box: it's a violation of relational database theory to
attempt to store more than one value in a single field, so Access doesn't
support it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to create a multiselect list box in a form. I
have made a list box listing the 5-6 choices and have
added an inidivual add and remove button, as well as
another list for the selected choices to go into. I am
able to select the items and move them to the new list
box. The problem I am having is that once I have selected
the items and saved the form, they do not go into the
field of table desired. The field is blank. What is
needed to save the selections to the field?

Below is the code I have so far for the event procedure.

Private Sub Add_Button_Click_Click()
Dim VaxListCounter As Integer, VaxCurrentCounter As
Integer
Dim VaxListItems As Integer, VaxCurrentItems As
Integer
Dim ListStr As String, FoundInList As Integer
VaxListItems =

• .ListCount - 1
  VaxCurrentItems = [Cap].ListCount - 1
  For VaxListCounter = 0 To VaxListItems
  If
  • .Selected(VaxListCounter) = True Then
    If IsNull([Cap].RowSource) Then
    ListStr =
    • .Column(0,
      VaxListCounter) & ";"
      [Cap].RowSource = ListStr
      Else
      FoundInList = False
      For VaxCurrentCounter = 0 To
      VaxCurrentItems
      If [Cap].Column(0, VaxCurrentCounter)
      =
      • .Column(0, VaxListCounter) Then
        FoundInList = True
        End If
        Next VaxCurrentCounter
        If Not FoundInList Then
        ListStr = [Cap].RowSource &
        • .Column(0, VaxListCounter) & ";"
          [Cap].RowSource = ""
          [Cap].RowSource = ListStr
          End If
          End If
          End If
          Next VaxListCounter
          
          End Sub
          
          Private Sub Remove_Button_Click_Click()
          Dim ListStr As String
          Dim VaxCurrentItems As Integer, VaxCurrentCounter As
          Integer
          Dim VaxListItems As Integer
          VaxListItems = [Cap].ListCount - 1
          ListStr = ""
          For VaxCurrentCounter = 0 To VaxListItems
          If [Cap].Selected(VaxCurrentCounter) = False Then
          ListStr = ListStr & [Cap].Column(0,
          VaxCurrentCounter) & ";"
          End If
          Next VaxCurrentCounter
          [Cap].RowSource = ""
          [Cap].RowSource = ListStr
          
          End Sub

• • • • • 
          
          
          .

 

[Douglas J. Steele]

The proper way is to have a second table, and store the multiple values in
the second table, related to a single entry in the first table. Take a look
at the Northwinds application that came with your version of Access. The
Orders and Order Details tables are an example, and the Orders form shows
how to handle populating the tables.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I was trying to assign the list box to the desired field
via the "control source" field in properties. I would
like a person to select as many values as possible and
then have them listed in a field of a table. (i.e. table
field would list such selected choices as "warehouse,
distribution center, contract warehouse". If I am not
able to have more than one value in a single field, what
other options are available. Oh and thanks for the reply
Doug.

Quinton

-----Original Message-----
How are you trying to put them into a table field? You can't bind a
multiselect list box: it's a violation of relational database theory to
attempt to store more than one value in a single field, so Access doesn't
support it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to create a multiselect list box in a form. I
have made a list box listing the 5-6 choices and have
added an inidivual add and remove button, as well as
another list for the selected choices to go into. I am
able to select the items and move them to the new list
box. The problem I am having is that once I have selected
the items and saved the form, they do not go into the
field of table desired. The field is blank. What is
needed to save the selections to the field?

Below is the code I have so far for the event procedure.

Private Sub Add_Button_Click_Click()
Dim VaxListCounter As Integer, VaxCurrentCounter As
Integer
Dim VaxListItems As Integer, VaxCurrentItems As
Integer
Dim ListStr As String, FoundInList As Integer
VaxListItems =

• .ListCount - 1
  VaxCurrentItems = [Cap].ListCount - 1
  For VaxListCounter = 0 To VaxListItems
  If
  • .Selected(VaxListCounter) = True Then
    If IsNull([Cap].RowSource) Then
    ListStr =
    • .Column(0,
      VaxListCounter) & ";"
      [Cap].RowSource = ListStr
      Else
      FoundInList = False
      For VaxCurrentCounter = 0 To
      VaxCurrentItems
      If [Cap].Column(0, VaxCurrentCounter)
      =
      • .Column(0, VaxListCounter) Then
        FoundInList = True
        End If
        Next VaxCurrentCounter
        If Not FoundInList Then
        ListStr = [Cap].RowSource &
        • .Column(0, VaxListCounter) & ";"
          [Cap].RowSource = ""
          [Cap].RowSource = ListStr
          End If
          End If
          End If
          Next VaxListCounter
          
          End Sub
          
          Private Sub Remove_Button_Click_Click()
          Dim ListStr As String
          Dim VaxCurrentItems As Integer, VaxCurrentCounter As
          Integer
          Dim VaxListItems As Integer
          VaxListItems = [Cap].ListCount - 1
          ListStr = ""
          For VaxCurrentCounter = 0 To VaxListItems
          If [Cap].Selected(VaxCurrentCounter) = False Then
          ListStr = ListStr & [Cap].Column(0,
          VaxCurrentCounter) & ";"
          End If
          Next VaxCurrentCounter
          [Cap].RowSource = ""
          [Cap].RowSource = ListStr
          
          End Sub

• • • • • 
          
          
          .

 

[mal]

Not sure if I completly understand but do you want multiple selections
to go into a single output field, or each selection to go into a
single field but then the other multiple selections to go into the
same field in different rows ?
It osund like you are able to get from the select box to the list box
(I've posted a multiselect combo example today) so isn't it just a
matter of binding the result listbox to a new table ?

 

[mal]

Not sure if I completly understand but do you want multiple selections
to go into a single output field, or each selection to go into a
single field but then the other multiple selections to go into the
same field in different rows ?
It osund like you are able to get from the select box to the list box
(I've posted a multiselect combo example today) so isn't it just a
matter of binding the result listbox to a new table ?

 

[mal]

Not sure if I completly understand but do you want multiple selections
to go into a single output field, or each selection to go into a
single field but then the other multiple selections to go into the
same field in different rows ?
It osund like you are able to get from the select box to the list box
(I've posted a multiselect combo example today) so isn't it just a
matter of binding the result listbox to a new table ?

 

[mal]

Not sure if I completly understand but do you want multiple selections
to go into a single output field, or each selection to go into a
single field but then the other multiple selections to go into the
same field in different rows ?
It osund like you are able to get from the select box to the list box
(I've posted a multiselect combo example today) so isn't it just a
matter of binding the result listbox to a new table ?

 

[Q]

Hi,

I would like multiple selections to go into a single
output field.(i.e. if a person selects warehouse, hub,
and bakery, all three will show up in a field like so:
warehouse, hub, bakery.)

I have a table generated where I want the selections to
go into. I just can't seem to get it to transfer the
selected items in the new list box to the table. Where is
your combo example located at? Thanks for your reply.