Multiple-select from list

thanks Mr.Roger,



"Roger Carlson" wrote:

Yes it is. It's essentially the same process as cascading combo boxes. On
my website (www.rogersaccesslibrary.com), is a small Access database sample
called "CascadingComboBoxes.mdb" which illustrates how to do this. The
Simple Listbox example would be the closest. Just replace the first listbox
with a combo box. You can find the sample he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=389.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
"Denver" wrote in message
...
just want to ask if it is possible to synchronize a combo box and a list
box?
inorder to limit my selection after the combo box selection

thanks



"Klatuu" wrote:

You can't use a multi select list box control as a bound control. It
will
always return a Null value.
I'm not sure I understand the issue.

"BethHill" wrote:

Thank you! I am now able to make multiple selections in my list box.
Now
the problem lies with the text that is in the list box. There is just
field
for the control source on the list box but multiple of records. The
field is
just an AutoNumber in the list box. I don't want to have to scroll
down all
the way through the list to choose the records I want to send to SQL
statement. Is there a way to make it wrap the text by using columns in
the
list box?

"Klatuu" wrote:

You will need a List Box control with the Multi Select property set
to
Extended. This will allow you to use the familiar, Click to get one,
Ctl
Click to add another, or Shift Click to select a range.

Now, you need a Row Source for the field. It should look something
like:

SELECT DISTINCT SomeFieldName FROM SomeTableName

This will return one occurance of every value in the field.

Once the user has selected a field, you can use the ItemsSelected
collection
of the List Box control to put together a where condition for your
SQL
statement:

Dim varItem As Variant
Dim strWhere as String

strWhere = "IN("
For Each varItem In Me.MyListBoxName.ItemsSelected
strWhere = strWhere & Chr(34) &
Me.MyListBoxName.ItemData(varItem) & _
Chr(34) & ","
Next varItem
'Take off the last comma
strWhere = Left(strWhere, Len(strWhere) -1)
'Add the closing paren
strWhere = strWhere & ")"

Now you have a string you can use in your SQL that will filter based
on the
selections maked in the list box.

"BethHill" wrote:

Hello,

I want to be able to make multiple selections on my form from the
same field
in the same table but allow specific records to be selected, as
well as a
range of records and certain records (comma delimited?). After the
fields
are selected, I want to pass this value into a SQL statement to
perform the
query. How do I do this?

Mr.Roger thanks a lot....
now i decide to use two list boxes so that it would be easy for me to try
working on the codes from the database i have downloaded.

now i have 2 list boxes the 1st one name 'Site Location' [ROWsOURCE]:SELECT
[Filtering Data].[Site Location] FROM [Filtering Data] GROUP BY [Site
Location];
EVENT : I just copy the code Me!List39 = Null
Me!List39.Requery

for the 2nd one name 'Select Drawing Type' [ROWsOURCE]:SELECT DISTINCTROW
[Filtering Data].[Drawing Type] FROM [Filtering Data] WHERE ((([Filtering
Data].[Drawing Type] LIKE [Forms]![Filtering Data]![List39]; EVENT - no event

the problem is i got error message............................

syntax error, the syntax is is query form WHERE ((([Filtering Data].[Drawing
Type] LIKE [Forms]![Filtering Data]![List39]

where is my mistakes here?do i miss something here? please help me...

thanks


"Roger Carlson" wrote:

Yes it is. It's essentially the same process as cascading combo boxes. On
my website (www.rogersaccesslibrary.com), is a small Access database sample
called "CascadingComboBoxes.mdb" which illustrates how to do this. The
Simple Listbox example would be the closest. Just replace the first listbox
with a combo box. You can find the sample he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=389.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
"Denver" wrote in message
...
just want to ask if it is possible to synchronize a combo box and a list
box?
inorder to limit my selection after the combo box selection

thanks



"Klatuu" wrote:

You can't use a multi select list box control as a bound control. It
will
always return a Null value.
I'm not sure I understand the issue.

"BethHill" wrote:

Thank you! I am now able to make multiple selections in my list box.
Now
the problem lies with the text that is in the list box. There is just
field
for the control source on the list box but multiple of records. The
field is
just an AutoNumber in the list box. I don't want to have to scroll
down all
the way through the list to choose the records I want to send to SQL
statement. Is there a way to make it wrap the text by using columns in
the
list box?

"Klatuu" wrote:

You will need a List Box control with the Multi Select property set
to
Extended. This will allow you to use the familiar, Click to get one,
Ctl
Click to add another, or Shift Click to select a range.

Now, you need a Row Source for the field. It should look something
like:

SELECT DISTINCT SomeFieldName FROM SomeTableName

This will return one occurance of every value in the field.

Once the user has selected a field, you can use the ItemsSelected
collection
of the List Box control to put together a where condition for your
SQL
statement:

Dim varItem As Variant
Dim strWhere as String

strWhere = "IN("
For Each varItem In Me.MyListBoxName.ItemsSelected
strWhere = strWhere & Chr(34) &
Me.MyListBoxName.ItemData(varItem) & _
Chr(34) & ","
Next varItem
'Take off the last comma
strWhere = Left(strWhere, Len(strWhere) -1)
'Add the closing paren
strWhere = strWhere & ")"

Now you have a string you can use in your SQL that will filter based
on the
selections maked in the list box.

"BethHill" wrote:

Hello,

I want to be able to make multiple selections on my form from the
same field
in the same table but allow specific records to be selected, as
well as a
range of records and certain records (comma delimited?). After the
fields
are selected, I want to pass this value into a SQL statement to
perform the
query. How do I do this?

I think Roger may have missed the fact that you're trying to use a list box
that allows multiselect. As I believe Dave told you earlier in this thread,
a multiselect list box returns Null, regardless of how many entries are
selected.

I don't have Roger's sample, so I can't give you explicit instructions.
However, in VBA, you need to create a list of the selected entries in the
list. To do that, you use code like:

Dim strSelected As String
Dim varSelected As Variant

If Me.List39.ItemsSelected.Count 0 Then
For Each varSelected In Me.List39.ItemsSelected
strSelected = strSelected & Me.List39.ItemData(varSelected) & ", "
Next varSelected
' Remove the extraneous comma and space from the end.
strSelected = Left(strSelected, Len(strSelected) - 2)
End If


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Denver" wrote in message
...
Mr.Roger thanks a lot....
now i decide to use two list boxes so that it would be easy for me to try
working on the codes from the database i have downloaded.

now i have 2 list boxes the 1st one name 'Site Location'
[ROWsOURCE]:SELECT
[Filtering Data].[Site Location] FROM [Filtering Data] GROUP BY [Site
Location];
EVENT : I just copy the code Me!List39 = Null
Me!List39.Requery

for the 2nd one name 'Select Drawing Type' [ROWsOURCE]:SELECT DISTINCTROW
[Filtering Data].[Drawing Type] FROM [Filtering Data] WHERE ((([Filtering
Data].[Drawing Type] LIKE [Forms]![Filtering Data]![List39]; EVENT - no
event

the problem is i got error message............................

syntax error, the syntax is is query form WHERE ((([Filtering
Data].[Drawing
Type] LIKE [Forms]![Filtering Data]![List39]

where is my mistakes here?do i miss something here? please help me...

thanks


"Roger Carlson" wrote:

Yes it is. It's essentially the same process as cascading combo boxes.
On
my website (www.rogersaccesslibrary.com), is a small Access database
sample
called "CascadingComboBoxes.mdb" which illustrates how to do this. The
Simple Listbox example would be the closest. Just replace the first
listbox
with a combo box. You can find the sample he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=389.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
"Denver" wrote in message
...
just want to ask if it is possible to synchronize a combo box and a
list
box?
inorder to limit my selection after the combo box selection

thanks



"Klatuu" wrote:

You can't use a multi select list box control as a bound control. It
will
always return a Null value.
I'm not sure I understand the issue.

"BethHill" wrote:

Thank you! I am now able to make multiple selections in my list
box.
Now
the problem lies with the text that is in the list box. There is
just
field
for the control source on the list box but multiple of records. The
field is
just an AutoNumber in the list box. I don't want to have to scroll
down all
the way through the list to choose the records I want to send to SQL
statement. Is there a way to make it wrap the text by using columns
in
the
list box?

"Klatuu" wrote:

You will need a List Box control with the Multi Select property
set
to
Extended. This will allow you to use the familiar, Click to get
one,
Ctl
Click to add another, or Shift Click to select a range.

Now, you need a Row Source for the field. It should look
something
like:

SELECT DISTINCT SomeFieldName FROM SomeTableName

This will return one occurance of every value in the field.

Once the user has selected a field, you can use the ItemsSelected
collection
of the List Box control to put together a where condition for your
SQL
statement:

Dim varItem As Variant
Dim strWhere as String

strWhere = "IN("
For Each varItem In Me.MyListBoxName.ItemsSelected
strWhere = strWhere & Chr(34) &
Me.MyListBoxName.ItemData(varItem) & _
Chr(34) & ","
Next varItem
'Take off the last comma
strWhere = Left(strWhere, Len(strWhere) -1)
'Add the closing paren
strWhere = strWhere & ")"

Now you have a string you can use in your SQL that will filter
based
on the
selections maked in the list box.

"BethHill" wrote:

Hello,

I want to be able to make multiple selections on my form from
the
same field
in the same table but allow specific records to be selected, as
well as a
range of records and certain records (comma delimited?). After
the
fields
are selected, I want to pass this value into a SQL statement to
perform the
query. How do I do this?

Doug,

What I understood Denver wanted was to limit the choices in a multi-select
listbox (that is the Row Source) based on the value selected in a combo box.
My suggestion of the sample was predicated on that assumption.

If Denver wants to limit the choices in a multi-select listbox based on
values selected in *another* multi-select listbox, then that is a different
story.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Douglas J. Steele" wrote in message
...
I think Roger may have missed the fact that you're trying to use a list box
that allows multiselect. As I believe Dave told you earlier in this thread,
a multiselect list box returns Null, regardless of how many entries are
selected.

I don't have Roger's sample, so I can't give you explicit instructions.
However, in VBA, you need to create a list of the selected entries in the
list. To do that, you use code like:

Dim strSelected As String
Dim varSelected As Variant

If Me.List39.ItemsSelected.Count 0 Then
For Each varSelected In Me.List39.ItemsSelected
strSelected = strSelected & Me.List39.ItemData(varSelected) & ", "
Next varSelected
' Remove the extraneous comma and space from the end.
strSelected = Left(strSelected, Len(strSelected) - 2)
End If


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Denver" wrote in message
...
Mr.Roger thanks a lot....
now i decide to use two list boxes so that it would be easy for me to try
working on the codes from the database i have downloaded.

now i have 2 list boxes the 1st one name 'Site Location'
[ROWsOURCE]:SELECT
[Filtering Data].[Site Location] FROM [Filtering Data] GROUP BY [Site
Location];
EVENT : I just copy the code Me!List39 = Null
Me!List39.Requery

for the 2nd one name 'Select Drawing Type' [ROWsOURCE]:SELECT DISTINCTROW
[Filtering Data].[Drawing Type] FROM [Filtering Data] WHERE ((([Filtering
Data].[Drawing Type] LIKE [Forms]![Filtering Data]![List39]; EVENT - no
event

the problem is i got error message............................

syntax error, the syntax is is query form WHERE ((([Filtering
Data].[Drawing
Type] LIKE [Forms]![Filtering Data]![List39]

where is my mistakes here?do i miss something here? please help me...

thanks


"Roger Carlson" wrote:

Yes it is. It's essentially the same process as cascading combo boxes.
On
my website (www.rogersaccesslibrary.com), is a small Access database
sample
called "CascadingComboBoxes.mdb" which illustrates how to do this. The
Simple Listbox example would be the closest. Just replace the first
listbox
with a combo box. You can find the sample he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=389.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
"Denver" wrote in message
...
just want to ask if it is possible to synchronize a combo box and a
list
box?
inorder to limit my selection after the combo box selection

thanks



"Klatuu" wrote:

You can't use a multi select list box control as a bound control. It
will
always return a Null value.
I'm not sure I understand the issue.

"BethHill" wrote:

Thank you! I am now able to make multiple selections in my list
box.
Now
the problem lies with the text that is in the list box. There is
just
field
for the control source on the list box but multiple of records.
The
field is
just an AutoNumber in the list box. I don't want to have to scroll
down all
the way through the list to choose the records I want to send to
SQL
statement. Is there a way to make it wrap the text by using
columns in
the
list box?

"Klatuu" wrote:

You will need a List Box control with the Multi Select property
set
to
Extended. This will allow you to use the familiar, Click to get
one,
Ctl
Click to add another, or Shift Click to select a range.

Now, you need a Row Source for the field. It should look
something
like:

SELECT DISTINCT SomeFieldName FROM SomeTableName

This will return one occurance of every value in the field.

Once the user has selected a field, you can use the ItemsSelected
collection
of the List Box control to put together a where condition for
your
SQL
statement:

Dim varItem As Variant
Dim strWhere as String

strWhere = "IN("
For Each varItem In Me.MyListBoxName.ItemsSelected
strWhere = strWhere & Chr(34) &
Me.MyListBoxName.ItemData(varItem) & _
Chr(34) & ","
Next varItem
'Take off the last comma
strWhere = Left(strWhere, Len(strWhere) -1)
'Add the closing paren
strWhere = strWhere & ")"

Now you have a string you can use in your SQL that will filter
based
on the
selections maked in the list box.

"BethHill" wrote:

Hello,

I want to be able to make multiple selections on my form from
the
same field
in the same table but allow specific records to be selected, as
well as a
range of records and certain records (comma delimited?). After
the
fields
are selected, I want to pass this value into a SQL statement to
perform the
query. How do I do this?

Before I can say more, are these BOTH multi-select listboxes? Because using
this strategy, at the very least, the first listbox must NOT me
multi-select. That is, only one value can be selected.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Denver" wrote in message
...
Mr.Roger thanks a lot....
now i decide to use two list boxes so that it would be easy for me to try
working on the codes from the database i have downloaded.

now i have 2 list boxes the 1st one name 'Site Location'
[ROWsOURCE]:SELECT
[Filtering Data].[Site Location] FROM [Filtering Data] GROUP BY [Site
Location];
EVENT : I just copy the code Me!List39 = Null
Me!List39.Requery

for the 2nd one name 'Select Drawing Type' [ROWsOURCE]:SELECT DISTINCTROW
[Filtering Data].[Drawing Type] FROM [Filtering Data] WHERE ((([Filtering
Data].[Drawing Type] LIKE [Forms]![Filtering Data]![List39]; EVENT - no
event

the problem is i got error message............................

syntax error, the syntax is is query form WHERE ((([Filtering
Data].[Drawing
Type] LIKE [Forms]![Filtering Data]![List39]

where is my mistakes here?do i miss something here? please help me...

thanks


"Roger Carlson" wrote:

Yes it is. It's essentially the same process as cascading combo boxes.
On
my website (www.rogersaccesslibrary.com), is a small Access database
sample
called "CascadingComboBoxes.mdb" which illustrates how to do this. The
Simple Listbox example would be the closest. Just replace the first
listbox
with a combo box. You can find the sample he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=389.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
"Denver" wrote in message
...
just want to ask if it is possible to synchronize a combo box and a
list
box?
inorder to limit my selection after the combo box selection

thanks



"Klatuu" wrote:

You can't use a multi select list box control as a bound control. It
will
always return a Null value.
I'm not sure I understand the issue.

"BethHill" wrote:

Thank you! I am now able to make multiple selections in my list
box.
Now
the problem lies with the text that is in the list box. There is
just
field
for the control source on the list box but multiple of records. The
field is
just an AutoNumber in the list box. I don't want to have to scroll
down all
the way through the list to choose the records I want to send to SQL
statement. Is there a way to make it wrap the text by using columns
in
the
list box?

"Klatuu" wrote:

You will need a List Box control with the Multi Select property
set
to
Extended. This will allow you to use the familiar, Click to get
one,
Ctl
Click to add another, or Shift Click to select a range.

Now, you need a Row Source for the field. It should look
something
like:

SELECT DISTINCT SomeFieldName FROM SomeTableName

This will return one occurance of every value in the field.

Once the user has selected a field, you can use the ItemsSelected
collection
of the List Box control to put together a where condition for your
SQL
statement:

Dim varItem As Variant
Dim strWhere as String

strWhere = "IN("
For Each varItem In Me.MyListBoxName.ItemsSelected
strWhere = strWhere & Chr(34) &
Me.MyListBoxName.ItemData(varItem) & _
Chr(34) & ","
Next varItem
'Take off the last comma
strWhere = Left(strWhere, Len(strWhere) -1)
'Add the closing paren
strWhere = strWhere & ")"

Now you have a string you can use in your SQL that will filter
based
on the
selections maked in the list box.

"BethHill" wrote:

Hello,

I want to be able to make multiple selections on my form from
the
same field
in the same table but allow specific records to be selected, as
well as a
range of records and certain records (comma delimited?). After
the
fields
are selected, I want to pass this value into a SQL statement to
perform the
query. How do I do this?

before anything else Mr.Douglas and Mr. Roger, thank you for being so
patience with me i really appreciate your time and understanding.... but all
your suggestion is very helpful to me as new to access 2003. hope you will be
understanding and kind to me until i finish my database...

on my first question is that i have a combo box and a list box and i want to
limit my choices after my selection in my combo box.... but as Mr. Roger give
one of his example so i decide to change my combo box into a list box so that
it would be easy for me to try working on those example.....

please base on the codes i have pasted here...i need your advice and help me
with my codes maybe i missed something.....

thanks

denver

"Douglas J. Steele" wrote:

I think Roger may have missed the fact that you're trying to use a list box
that allows multiselect. As I believe Dave told you earlier in this thread,
a multiselect list box returns Null, regardless of how many entries are
selected.

I don't have Roger's sample, so I can't give you explicit instructions.
However, in VBA, you need to create a list of the selected entries in the
list. To do that, you use code like:

Dim strSelected As String
Dim varSelected As Variant

If Me.List39.ItemsSelected.Count 0 Then
For Each varSelected In Me.List39.ItemsSelected
strSelected = strSelected & Me.List39.ItemData(varSelected) & ", "
Next varSelected
' Remove the extraneous comma and space from the end.
strSelected = Left(strSelected, Len(strSelected) - 2)
End If


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Denver" wrote in message
...
Mr.Roger thanks a lot....
now i decide to use two list boxes so that it would be easy for me to try
working on the codes from the database i have downloaded.

now i have 2 list boxes the 1st one name 'Site Location'
[ROWsOURCE]:SELECT
[Filtering Data].[Site Location] FROM [Filtering Data] GROUP BY [Site
Location];
EVENT : I just copy the code Me!List39 = Null
Me!List39.Requery

for the 2nd one name 'Select Drawing Type' [ROWsOURCE]:SELECT DISTINCTROW
[Filtering Data].[Drawing Type] FROM [Filtering Data] WHERE ((([Filtering
Data].[Drawing Type] LIKE [Forms]![Filtering Data]![List39]; EVENT - no
event

the problem is i got error message............................

syntax error, the syntax is is query form WHERE ((([Filtering
Data].[Drawing
Type] LIKE [Forms]![Filtering Data]![List39]

where is my mistakes here?do i miss something here? please help me...

thanks


"Roger Carlson" wrote:

Yes it is. It's essentially the same process as cascading combo boxes.
On
my website (www.rogersaccesslibrary.com), is a small Access database
sample
called "CascadingComboBoxes.mdb" which illustrates how to do this. The
Simple Listbox example would be the closest. Just replace the first
listbox
with a combo box. You can find the sample he
http://www.rogersaccesslibrary.com/f...ts.asp?TID=389.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
"Denver" wrote in message
...
just want to ask if it is possible to synchronize a combo box and a
list
box?
inorder to limit my selection after the combo box selection

thanks



"Klatuu" wrote:

You can't use a multi select list box control as a bound control. It
will
always return a Null value.
I'm not sure I understand the issue.

"BethHill" wrote:

Thank you! I am now able to make multiple selections in my list
box.
Now
the problem lies with the text that is in the list box. There is
just
field
for the control source on the list box but multiple of records. The
field is
just an AutoNumber in the list box. I don't want to have to scroll
down all
the way through the list to choose the records I want to send to SQL
statement. Is there a way to make it wrap the text by using columns
in
the
list box?

"Klatuu" wrote:

You will need a List Box control with the Multi Select property
set
to
Extended. This will allow you to use the familiar, Click to get
one,
Ctl
Click to add another, or Shift Click to select a range.

Now, you need a Row Source for the field. It should look
something
like:

SELECT DISTINCT SomeFieldName FROM SomeTableName

This will return one occurance of every value in the field.

Once the user has selected a field, you can use the ItemsSelected
collection
of the List Box control to put together a where condition for your
SQL
statement:

Dim varItem As Variant
Dim strWhere as String

strWhere = "IN("
For Each varItem In Me.MyListBoxName.ItemsSelected
strWhere = strWhere & Chr(34) &
Me.MyListBoxName.ItemData(varItem) & _
Chr(34) & ","
Next varItem
'Take off the last comma
strWhere = Left(strWhere, Len(strWhere) -1)
'Add the closing paren
strWhere = strWhere & ")"

Now you have a string you can use in your SQL that will filter
based
on the
selections maked in the list box.

"BethHill" wrote:

Hello,

I want to be able to make multiple selections on my form from
the
same field
in the same table but allow specific records to be selected, as
well as a
range of records and certain records (comma delimited?). After
the
fields
are selected, I want to pass this value into a SQL statement to
perform the
query. How do I do this?