Multiselect List Box-Access 2002

  • Thread starter Thread starter Q
  • Start date Start date
Q

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
 
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.
 
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!)



Q said:
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
Click to expand...



          • .
Click to expand...
 
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!)



Q said:
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!)



Q said:
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
Click to expand...



          • .
Click to expand...
Click to expand...
 
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 ?
 
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 ?
 
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 ?
 
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 ?
 
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.
 
Back
Top