Multiple selections in a list box

[Luther]

Hello everyone,

How can I get a list box multiple selections to store in
the underlying table; I have it's Control Source set to a
field named "SOURCE" in the table, but when I test it by
selecting 2 or 3 items, nothing happens !!! I would like
the table to show something like A;B;C;D in that field.

Any help would be appreciated.

Thanks.

 

[Sandra Daigle]

Hi Luther,

You have to write some code to do this. For example, the following will take
the selected items from list0 and string them together in a textbox:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.MyText = Me.MyText & .ItemData(varItem) & ", "
Next varItem
End With
Me.MyText = Left(Me.MyText, Len(Me.MyText) - 2)

To get the value from the second column you could modify this to:

 

[Sandra Daigle]

Sorry - hit send too soon -

You have to write some code to do this. For example, the following will take
the selected items from list0 and string them together in a textbox:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.MyText = Me.MyText & .ItemData(varItem) & ", "
Next varItem
End With
Me.MyText = Left(Me.MyText, Len(Me.MyText) - 2)

To get the value from the second column you could modify this to:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.Text5 = Me.Text5 & .Column(1, varItem) & ", "
Next varItem
End With
Me.Text5 = Left(Me.Text5, Len(Me.Text5) - 2)

Now, having said all this I'd suggest not aggregating values into a single
string like this. It's not normalized and eventually, you are going to need
to break that string appart to make use of the data in it. You would really
be better off creating a new row in a related table for each selection. Give
more information about your table if you'd like some help doing this. The
concept is the same - you loop through the ItemsSelected collection and
instead of building a string, you insert records in a table.

 

[Guest]

Thank you, Sandra. This seem to work well with an error
For example, if I select A,B,D,E in the first record,
EVERY OTHER record retains that selection. Any way to
clear this? Also give a compile error where there is an
invalid use of Null (last line of code)

I am not sure what kind of information you would need
about the table; the goal is for somebody to look at this
field and make a decision about that record.

Out of curiosity, is there any way to display the list box
horizontal vs. vertical (running out of room !!)

-----Original Message-----
Sorry - hit send too soon -

You have to write some code to do this. For example, the following will take
the selected items from list0 and string them together in a textbox:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.MyText = Me.MyText & .ItemData(varItem) & ", "
Next varItem
End With
Me.MyText = Left(Me.MyText, Len(Me.MyText) - 2)

To get the value from the second column you could modify this to:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.Text5 = Me.Text5 & .Column(1, varItem) & ", "
Next varItem
End With
Me.Text5 = Left(Me.Text5, Len(Me.Text5) - 2)

Now, having said all this I'd suggest not aggregating values into a single
string like this. It's not normalized and eventually, you are going to need
to break that string appart to make use of the data in it. You would really
be better off creating a new row in a related table for each selection. Give
more information about your table if you'd like some help doing this. The
concept is the same - you loop through the ItemsSelected collection and
instead of building a string, you insert records in a table.


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hello everyone,

How can I get a list box multiple selections to store in
the underlying table; I have it's Control Source set to a
field named "SOURCE" in the table, but when I test it by
selecting 2 or 3 items, nothing happens !!! I would like
the table to show something like A;B;C;D in that field.

Any help would be appreciated.

Thanks.

.

 

[Sandra Daigle]

Hi Luther,

To clear the textbox just assign the null value to it:

me.text5=null

If this is an unbound textbox you would probably want to do this in the
current event of the form so that it is wiped after any record navigation.

To avoid the runtime error with null just modify the last line to be the
following:

if not me.text5 is null then
Me.Text5 = Left(Me.Text5, Len(Me.Text5) - 2)
endif

Listboxes are always vertical but they have scrollbars so room shouldn't be
an issue as long as you have space enough to show 2 or 3 rows.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Thank you, Sandra. This seem to work well with an error
For example, if I select A,B,D,E in the first record,
EVERY OTHER record retains that selection. Any way to
clear this? Also give a compile error where there is an
invalid use of Null (last line of code)

I am not sure what kind of information you would need
about the table; the goal is for somebody to look at this
field and make a decision about that record.

Out of curiosity, is there any way to display the list box
horizontal vs. vertical (running out of room !!)

-----Original Message-----
Sorry - hit send too soon -

You have to write some code to do this. For example, the following will
take the selected items from list0 and string them together in a textbox:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.MyText = Me.MyText & .ItemData(varItem) & ", "
Next varItem
End With
Me.MyText = Left(Me.MyText, Len(Me.MyText) - 2)

To get the value from the second column you could modify this to:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.Text5 = Me.Text5 & .Column(1, varItem) & ", "
Next varItem
End With
Me.Text5 = Left(Me.Text5, Len(Me.Text5) - 2)

Now, having said all this I'd suggest not aggregating values into a
single string like this. It's not normalized and eventually, you are
going to need to break that string appart to make use of the data in it. You would really
be better off creating a new row in a related table for each selection.
Give more information about your table if you'd like some help doing
this. The concept is the same - you loop through the ItemsSelected
collection and instead of building a string, you insert records in a
table.


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hello everyone,

How can I get a list box multiple selections to store in
the underlying table; I have it's Control Source set to a
field named "SOURCE" in the table, but when I test it by
selecting 2 or 3 items, nothing happens !!! I would like
the table to show something like A;B;C;D in that field.

Any help would be appreciated.

Thanks.

.

 

[Luther]

Hi Sandra,

Unfortunately, the field SOURCE only need one column to
pick up the data...I tried your modifications below and
still didn't work. You are right about having to break
that string apart...I was just wondering about your
suggestions of building a related table that would contain
the selections. Can we do something like that, please?
Again, what kind of info you would need about the table?

-----Original Message-----
Hi Luther,

To clear the textbox just assign the null value to it:

me.text5=null

If this is an unbound textbox you would probably want to do this in the
current event of the form so that it is wiped after any record navigation.

To avoid the runtime error with null just modify the last line to be the
following:

if not me.text5 is null then
Me.Text5 = Left(Me.Text5, Len(Me.Text5) - 2)
endif

Listboxes are always vertical but they have scrollbars so room shouldn't be
an issue as long as you have space enough to show 2 or 3 rows.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Thank you, Sandra. This seem to work well with an error
For example, if I select A,B,D,E in the first record,
EVERY OTHER record retains that selection. Any way to
clear this? Also give a compile error where there is an
invalid use of Null (last line of code)

I am not sure what kind of information you would need
about the table; the goal is for somebody to look at this
field and make a decision about that record.

Out of curiosity, is there any way to display the list box
horizontal vs. vertical (running out of room !!)

-----Original Message-----
Sorry - hit send too soon -

You have to write some code to do this. For example, the following will
take the selected items from list0 and string them together in a textbox:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.MyText = Me.MyText & .ItemData(varItem) & ", "
Next varItem
End With
Me.MyText = Left(Me.MyText, Len(Me.MyText) - 2)

To get the value from the second column you could modify this to:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.Text5 = Me.Text5 & .Column(1, varItem) & ", "
Next varItem
End With
Me.Text5 = Left(Me.Text5, Len(Me.Text5) - 2)

Now, having said all this I'd suggest not aggregating values into a
single string like this. It's not normalized and eventually, you are
going to need to break that string appart to make use

of the data in
it. You would really

be better off creating a new row in a related table for each selection.
Give more information about your table if you'd like some help doing
this. The concept is the same - you loop through the ItemsSelected
collection and instead of building a string, you insert records in a
table.


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Luther wrote:
Hello everyone,

How can I get a list box multiple selections to store in
the underlying table; I have it's Control Source set to a
field named "SOURCE" in the table, but when I test it by
selecting 2 or 3 items, nothing happens !!! I would like
the table to show something like A;B;C;D in that field.

Any help would be appreciated.

Thanks.


.

.

 

[Sandra Daigle]

What is the table that is used in the mainform? Basically the second table
needs to have a Foreign Key field which relates back to the table in the
main form and at least one other field to hold this 'Source' value.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra,

Unfortunately, the field SOURCE only need one column to
pick up the data...I tried your modifications below and
still didn't work. You are right about having to break
that string apart...I was just wondering about your
suggestions of building a related table that would contain
the selections. Can we do something like that, please?
Again, what kind of info you would need about the table?

-----Original Message-----
Hi Luther,

To clear the textbox just assign the null value to it:

me.text5=null

If this is an unbound textbox you would probably want to do this in
the current event of the form so that it is wiped after any record
navigation.

To avoid the runtime error with null just modify the last line to be
the following:

if not me.text5 is null then
Me.Text5 = Left(Me.Text5, Len(Me.Text5) - 2)
endif

Listboxes are always vertical but they have scrollbars so room
shouldn't be an issue as long as you have space enough to show 2 or
3 rows.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Thank you, Sandra. This seem to work well with an error
For example, if I select A,B,D,E in the first record,
EVERY OTHER record retains that selection. Any way to
clear this? Also give a compile error where there is an
invalid use of Null (last line of code)

I am not sure what kind of information you would need
about the table; the goal is for somebody to look at this
field and make a decision about that record.

Out of curiosity, is there any way to display the list box
horizontal vs. vertical (running out of room !!)

-----Original Message-----
Sorry - hit send too soon -

You have to write some code to do this. For example, the following
will take the selected items from list0 and string them together
in a textbox:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.MyText = Me.MyText & .ItemData(varItem) & ", "
Next varItem
End With
Me.MyText = Left(Me.MyText, Len(Me.MyText) - 2)

To get the value from the second column you could modify this to:

Dim varItem As Variant
With Me.List0
For Each varItem In .ItemsSelected
Me.Text5 = Me.Text5 & .Column(1, varItem) & ", "
Next varItem
End With
Me.Text5 = Left(Me.Text5, Len(Me.Text5) - 2)

Now, having said all this I'd suggest not aggregating values into a
single string like this. It's not normalized and eventually, you
are going to need to break that string appart to make use of the
data in it. You would really be better off creating a new row in a
related table for each selection. Give more information about your
table if you'd like some help doing this. The concept is the same
- you loop through the ItemsSelected collection and instead of
building a string, you insert records in a table.


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Luther wrote:
Hello everyone,

How can I get a list box multiple selections to store in
the underlying table; I have it's Control Source set to a
field named "SOURCE" in the table, but when I test it by
selecting 2 or 3 items, nothing happens !!! I would like
the table to show something like A;B;C;D in that field.

Any help would be appreciated.

Thanks.


.

.