Multiple-select from list

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?

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?

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?

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?

Let me give you my scenario:

In the form, there is a multi-select list box with the control source as
"SELECT [tbl Event].[Event ID] FROM [tbl Event]; " This correctly displays
all the records for the Event ID field.

In this list box, there are many records that I have to scroll through in
order to select the ones that I want. I was wondering if there is a way to
display more records in the list box rather than scroll through each of them.



"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?

If you are talking about putting multiple [Event ID]s in the list box item (1
id per column), it is possible, but not easy, and would be difficult to
select. How would the user know which column to select? How would you, as
the developer, know which column to use? Is the [Event ID] text that is
meaningful to a user? Is it unique? How many are we talking about?

"BethHill" wrote:

Let me give you my scenario:

In the form, there is a multi-select list box with the control source as
"SELECT [tbl Event].[Event ID] FROM [tbl Event]; " This correctly displays
all the records for the Event ID field.

In this list box, there are many records that I have to scroll through in
order to select the ones that I want. I was wondering if there is a way to
display more records in the list box rather than scroll through each of them.



"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?

The [Event ID] text is very meaningful to the user and is unique. (It is the
primary key in the "tbl Event" table.) There are currently about 100+
records or [Event ID]s that the user could choose from but could be more very
soon. If columns is not a good alternative, do you know of another option?

"Klatuu" wrote:

If you are talking about putting multiple [Event ID]s in the list box item (1
id per column), it is possible, but not easy, and would be difficult to
select. How would the user know which column to select? How would you, as
the developer, know which column to use? Is the [Event ID] text that is
meaningful to a user? Is it unique? How many are we talking about?

"BethHill" wrote:

Let me give you my scenario:

In the form, there is a multi-select list box with the control source as
"SELECT [tbl Event].[Event ID] FROM [tbl Event]; " This correctly displays
all the records for the Event ID field.

In this list box, there are many records that I have to scroll through in
order to select the ones that I want. I was wondering if there is a way to
display more records in the list box rather than scroll through each of them.



"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?

There is not a better solution that I can think of. I tried using a combo
with auto expand to position the list box, but anything previously selected
became unselected.

"BethHill" wrote:

The [Event ID] text is very meaningful to the user and is unique. (It is the
primary key in the "tbl Event" table.) There are currently about 100+
records or [Event ID]s that the user could choose from but could be more very
soon. If columns is not a good alternative, do you know of another option?

"Klatuu" wrote:

If you are talking about putting multiple [Event ID]s in the list box item (1
id per column), it is possible, but not easy, and would be difficult to
select. How would the user know which column to select? How would you, as
the developer, know which column to use? Is the [Event ID] text that is
meaningful to a user? Is it unique? How many are we talking about?

"BethHill" wrote:

Let me give you my scenario:

In the form, there is a multi-select list box with the control source as
"SELECT [tbl Event].[Event ID] FROM [tbl Event]; " This correctly displays
all the records for the Event ID field.

In this list box, there are many records that I have to scroll through in
order to select the ones that I want. I was wondering if there is a way to
display more records in the list box rather than scroll through each of them.



"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?

Could I use something else, like a text box and have the user to sepearte the
choices with commas or do a range with a hyphen? Similar to print options
when selecting the pages to be printed. If so, do you have the code to do
this?

"Klatuu" wrote:

There is not a better solution that I can think of. I tried using a combo
with auto expand to position the list box, but anything previously selected
became unselected.

"BethHill" wrote:

The [Event ID] text is very meaningful to the user and is unique. (It is the
primary key in the "tbl Event" table.) There are currently about 100+
records or [Event ID]s that the user could choose from but could be more very
soon. If columns is not a good alternative, do you know of another option?

"Klatuu" wrote:

If you are talking about putting multiple [Event ID]s in the list box item (1
id per column), it is possible, but not easy, and would be difficult to
select. How would the user know which column to select? How would you, as
the developer, know which column to use? Is the [Event ID] text that is
meaningful to a user? Is it unique? How many are we talking about?

"BethHill" wrote:

Let me give you my scenario:

In the form, there is a multi-select list box with the control source as
"SELECT [tbl Event].[Event ID] FROM [tbl Event]; " This correctly displays
all the records for the Event ID field.

In this list box, there are many records that I have to scroll through in
order to select the ones that I want. I was wondering if there is a way to
display more records in the list box rather than scroll through each of them.



"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 guess you could, but I really think that would take more time and be more
prone to errors. Also, remember, you want to be able to select a range or
use ctlClick to select multiple noncontiguous items. You would not be able
to do that easily.

I really believe the multi select list box is the easiest to develop and
would be the most user friendly.

"BethHill" wrote:

Could I use something else, like a text box and have the user to sepearte the
choices with commas or do a range with a hyphen? Similar to print options
when selecting the pages to be printed. If so, do you have the code to do
this?

"Klatuu" wrote:

There is not a better solution that I can think of. I tried using a combo
with auto expand to position the list box, but anything previously selected
became unselected.

"BethHill" wrote:

The [Event ID] text is very meaningful to the user and is unique. (It is the
primary key in the "tbl Event" table.) There are currently about 100+
records or [Event ID]s that the user could choose from but could be more very
soon. If columns is not a good alternative, do you know of another option?

"Klatuu" wrote:

If you are talking about putting multiple [Event ID]s in the list box item (1
id per column), it is possible, but not easy, and would be difficult to
select. How would the user know which column to select? How would you, as
the developer, know which column to use? Is the [Event ID] text that is
meaningful to a user? Is it unique? How many are we talking about?

"BethHill" wrote:

Let me give you my scenario:

In the form, there is a multi-select list box with the control source as
"SELECT [tbl Event].[Event ID] FROM [tbl Event]; " This correctly displays
all the records for the Event ID field.

In this list box, there are many records that I have to scroll through in
order to select the ones that I want. I was wondering if there is a way to
display more records in the list box rather than scroll through each of them.



"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?