Display List Box Items Selected

[Guest]

I have a list box called lstRigs. The user can make multiple selections.
Once the selections are made I would like to display just the selections made
on the form and have the list box itself invisible (as I have several list
box options on my Job form). From reading all the post I get the impression
I should create a second list box to hold a value list and then display "all"
from the value list. Is that right? I was able to to create the lstRigs list
box but I don't know how to set up the second list box. Not good at VBA so
please keep it simple.

 

[Graham Mandeno]

Hi Bobbye

If I understand you correctly, you want your second listbox to display only
those items which are selected in the first. You WILL need to use some VBA,
but it's not too scary, so bear with me

You can use the AfterUpdate event of the first listbox to construct a value
list for the RowSource of the second listbox. You must set the
RowSourceType of the second listbox to "Value List". Now, let's say your
listboxes are named "lstBox1" and "lstBox2" (of course your swill have much
more descriptive names!) You can then use the following code:

Private Sub lstBox1_AfterUpdate()
Dim strRowSource As String, varItem As Variant
With lstBox1
For Each varItem In .ItemsSelected
strRowSource = strRowSource & .ItemData(varItem) & ";"
Next varItem
End With
'remove last ";"
If Len(strRowSource) <> 0 Then
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
End If
lstBox2.RowSource = strRowSource
End Sub

What it does is create a string consisting of all the selected items from
lstBox1, separated by semicolons, and assigns the result to the RowSource of
lstBox2.

 

[fredg]

I have a list box called lstRigs. The user can make multiple selections.
Once the selections are made I would like to display just the selections made
on the form and have the list box itself invisible (as I have several list
box options on my Job form). From reading all the post I get the impression
I should create a second list box to hold a value list and then display "all"
from the value list. Is that right? I was able to to create the lstRigs list
box but I don't know how to set up the second list box. Not good at VBA so
please keep it simple.

If all you wish to do with the selections is to display them and then
hide the list box, you can use a regular unbound text control (named
"ShowData" below) to display the values selected.

Code the list box's Exit event:

Dim varItem As Variant
For Each varItem In ListBoxName.ItemsSelected
[ShowData] = Me![ShowData] & Me![ListBoxName].ItemData(varItem) &
vbNwLine
Next varItem

Me![AnyOtherControl].SetFocus
Me![ListBoxName].Visible = False

Each value will display on a new line, and the list box will no longer
be visible.

 

[Guest]

Graham
I had a data type mismatch. strRowSource as string needs to be an number
but I don't know what to change in the code you wrote for me.

--
Access 2000-03 User, Thanks in advance Bobbye

Hi Bobbye

If I understand you correctly, you want your second listbox to display only
those items which are selected in the first. You WILL need to use some VBA,
but it's not too scary, so bear with me

You can use the AfterUpdate event of the first listbox to construct a value
list for the RowSource of the second listbox. You must set the
RowSourceType of the second listbox to "Value List". Now, let's say your
listboxes are named "lstBox1" and "lstBox2" (of course your swill have much
more descriptive names!) You can then use the following code:

Private Sub lstBox1_AfterUpdate()
Dim strRowSource As String, varItem As Variant
With lstBox1
For Each varItem In .ItemsSelected
strRowSource = strRowSource & .ItemData(varItem) & ";"
Next varItem
End With
'remove last ";"
If Len(strRowSource) <> 0 Then
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
End If
lstBox2.RowSource = strRowSource
End Sub

What it does is create a string consisting of all the selected items from
lstBox1, separated by semicolons, and assigns the result to the RowSource of
lstBox2.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a list box called lstRigs. The user can make multiple selections.
Once the selections are made I would like to display just the selections
made
on the form and have the list box itself invisible (as I have several list
box options on my Job form). From reading all the post I get the
impression
I should create a second list box to hold a value list and then display
"all"
from the value list. Is that right? I was able to to create the lstRigs
list
box but I don't know how to set up the second list box. Not good at VBA
so
please keep it simple.

 

[Guest]

Fred and Graham
I will try the code you wrote. As I look at it I'm wondering if I've asked
the right question. What I'm working on is a job sheet. There are many
categories that the user needs to select from. (I fiqured if I could learn
how to do one it would apply to them all) Anyway, a job sheet is
automatically assigned a job number. All the rigs are listed in a list box
by rig number and name (2 columns with rig number being the primary). The
user can choose one or many rigs as needed. I not only want to display the
selections made but save them also. Does this change everything?
--
Access 2000 User, Thanks in advance Bobbye

I have a list box called lstRigs. The user can make multiple selections.
Once the selections are made I would like to display just the selections made
on the form and have the list box itself invisible (as I have several list
box options on my Job form). From reading all the post I get the impression
I should create a second list box to hold a value list and then display "all"
from the value list. Is that right? I was able to to create the lstRigs list
box but I don't know how to set up the second list box. Not good at VBA so
please keep it simple.

If all you wish to do with the selections is to display them and then
hide the list box, you can use a regular unbound text control (named
"ShowData" below) to display the values selected.

Code the list box's Exit event:

Dim varItem As Variant
For Each varItem In ListBoxName.ItemsSelected
[ShowData] = Me![ShowData] & Me![ListBoxName].ItemData(varItem) &
vbNwLine
Next varItem

Me![AnyOtherControl].SetFocus
Me![ListBoxName].Visible = False

Each value will display on a new line, and the list box will no longer
be visible.

 

[Graham Mandeno]

Hi Bobbye

What you have here is a "many-to-many" relationship: each job may involve
many rigs and each rig may be used for many jobs.

A relational database does not allow for a many-to-many relationship between
two tables, so for this you need a third "junction table" of job/rig pairs.

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form. My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named "MtoMListHandler" -
just look at the forms.

One caveat: at the moment, the primary keys of both your tables (jobs and
rigs) must be numeric, but it sounds like that is the case anyway.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Fred and Graham
I will try the code you wrote. As I look at it I'm wondering if I've
asked
the right question. What I'm working on is a job sheet. There are many
categories that the user needs to select from. (I fiqured if I could
learn
how to do one it would apply to them all) Anyway, a job sheet is
automatically assigned a job number. All the rigs are listed in a list
box
by rig number and name (2 columns with rig number being the primary). The
user can choose one or many rigs as needed. I not only want to display
the
selections made but save them also. Does this change everything?
--
Access 2000 User, Thanks in advance Bobbye

I have a list box called lstRigs. The user can make multiple
selections.
Once the selections are made I would like to display just the
selections made
on the form and have the list box itself invisible (as I have several
list
box options on my Job form). From reading all the post I get the
impression
I should create a second list box to hold a value list and then display
"all"
from the value list. Is that right? I was able to to create the
lstRigs list
box but I don't know how to set up the second list box. Not good at
VBA so
please keep it simple.

If all you wish to do with the selections is to display them and then
hide the list box, you can use a regular unbound text control (named
"ShowData" below) to display the values selected.

Code the list box's Exit event:

Dim varItem As Variant
For Each varItem In ListBoxName.ItemsSelected
[ShowData] = Me![ShowData] & Me![ListBoxName].ItemData(varItem) &
vbNwLine
Next varItem

Me![AnyOtherControl].SetFocus
Me![ListBoxName].Visible = False

Each value will display on a new line, and the list box will no longer
be visible.

 

[Guest]

I'll check out your suggestion. Thanks ever so much.
--
Access 2000 User, Thanks in advance Bobbye

Hi Bobbye

What you have here is a "many-to-many" relationship: each job may involve
many rigs and each rig may be used for many jobs.

A relational database does not allow for a many-to-many relationship between
two tables, so for this you need a third "junction table" of job/rig pairs.

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form. My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named "MtoMListHandler" -
just look at the forms.

One caveat: at the moment, the primary keys of both your tables (jobs and
rigs) must be numeric, but it sounds like that is the case anyway.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Fred and Graham
I will try the code you wrote. As I look at it I'm wondering if I've
asked
the right question. What I'm working on is a job sheet. There are many
categories that the user needs to select from. (I fiqured if I could
learn
how to do one it would apply to them all) Anyway, a job sheet is
automatically assigned a job number. All the rigs are listed in a list
box
by rig number and name (2 columns with rig number being the primary). The
user can choose one or many rigs as needed. I not only want to display
the
selections made but save them also. Does this change everything?
--
Access 2000 User, Thanks in advance Bobbye

On Sun, 21 Oct 2007 13:24:01 -0700, Bobbye R wrote:

I have a list box called lstRigs. The user can make multiple
selections.
Once the selections are made I would like to display just the
selections made
on the form and have the list box itself invisible (as I have several
list
box options on my Job form). From reading all the post I get the
impression
I should create a second list box to hold a value list and then display
"all"
from the value list. Is that right? I was able to to create the
lstRigs list
box but I don't know how to set up the second list box. Not good at
VBA so
please keep it simple.

If all you wish to do with the selections is to display them and then
hide the list box, you can use a regular unbound text control (named
"ShowData" below) to display the values selected.

Code the list box's Exit event:

Dim varItem As Variant
For Each varItem In ListBoxName.ItemsSelected
[ShowData] = Me![ShowData] & Me![ListBoxName].ItemData(varItem) &
vbNwLine
Next varItem

Me![AnyOtherControl].SetFocus
Me![ListBoxName].Visible = False

Each value will display on a new line, and the list box will no longer
be visible.