Multi Select List Boxes

Hello!

I have a form set up to narrow down the contents of one list box based
on another list box (Alan Browne's codes...). I have been trying to
adapt the codes for a multi select combo box but have not been able to
have contents pop up in the second list box based upon multiple entries
selected in the first list box...also every time I do multiple select
and try to filter records an error saying "no criteria" pops up. I
also have not been able to adapt this to select ALL...please help!

Multi Select List Boxes are quite different best than Combo Boxes or Single
Select List Boxes. They do not return a value. If you code something like:

strValue = Me.lstSomething

you wil usually trhow an error, because it will return Null. A Multi Select
List Box has an ItemsSelected property that contains a list of then items
that have been selected.

What you have to do is build a Where clause for a query using the
ItemsSelected property. If you look in VBA Help for ItemsSelected, you will
find an example of how to iterate through the selection. The technique is to
build the Where Clause and concatenate it with the query for your second List
Box Row Source. Then requery the second list box.

"Annemarie" wrote:

Hello!

I have a form set up to narrow down the contents of one list box based
on another list box (Alan Browne's codes...). I have been trying to
adapt the codes for a multi select combo box but have not been able to
have contents pop up in the second list box based upon multiple entries
selected in the first list box...also every time I do multiple select
and try to filter records an error saying "no criteria" pops up. I
also have not been able to adapt this to select ALL...please help!

I understand the concept I just cant quite figure out the actual doing
it... First i cannot find the itemsSelected property...and I am not
very experienced with coding.

I looked through the help section for ItemsSelected but the examples
were only for printing based on what was selected.

Sorry I am pretty lost on this.......

Ignore the printing what is selected. That is only an example. The
important part is how you iterate through the collection. You wont find that
property in the Properties Dialog. It is available only in VBA. Here is an
example:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

It passes back a string that will later be used to construct the Where
condition.

What I am doing is a little more complex that what you need, but as an
example, here is where I actually construt the row source for the list box
based on the selections made in the previous list box.

With Me.lstActivity
strWhere = FindWhere(4)
If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT Activity " & _
"FROM quniUPOLists " & _
strWhere & _
"ORDER BY Activity;"
End If
If .ListCount = 0 Then
Me.lblActivity.Caption = "No Matches"
Me.lblActivity.ForeColor = 255
.Height = 0
Me.cmdBillNetwork.SetFocus
Else
.Height = 3015
.SetFocus
End If
End With
DoCmd.Hourglass False


"Annemarie" wrote:

I understand the concept I just cant quite figure out the actual doing
it... First i cannot find the itemsSelected property...and I am not
very experienced with coding.

I looked through the help section for ItemsSelected but the examples
were only for printing based on what was selected.

Sorry I am pretty lost on this.......

I added the buildwherecondition to my form...
my second list box already had a record source like follows..

SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] WHERE ((([bfr
data].sa) Like forms![search criteria test]!txtfiltersa));

Now that i have changed it to the multi select it does not pull any
records for the second list box.

I have tried changing your record source codes around and adding them
in after update, before update, etc and I must just be missing
something. any suggestions?

I might be a lost cause....



Klatuu wrote:
Ignore the printing what is selected. That is only an example. The
important part is how you iterate through the collection. You wont find that
property in the Properties Dialog. It is available only in VBA. Here is an
example:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

It passes back a string that will later be used to construct the Where
condition.

What I am doing is a little more complex that what you need, but as an
example, here is where I actually construt the row source for the list box
based on the selections made in the previous list box.

With Me.lstActivity
strWhere = FindWhere(4)
If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT Activity " & _
"FROM quniUPOLists " & _
strWhere & _
"ORDER BY Activity;"
End If
If .ListCount = 0 Then
Me.lblActivity.Caption = "No Matches"
Me.lblActivity.ForeColor = 255
.Height = 0
Me.cmdBillNetwork.SetFocus
Else
.Height = 3015
.SetFocus
End If
End With
DoCmd.Hourglass False


"Annemarie" wrote:

I understand the concept I just cant quite figure out the actual doing
it... First i cannot find the itemsSelected property...and I am not
very experienced with coding.

I looked through the help section for ItemsSelected but the examples
were only for printing based on what was selected.

Sorry I am pretty lost on this.......

The trick is to start with your SELECT statement without a Where condition:

strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] "

Then, once the Where part has been created

strSQL = strSQL & strWhere

Of course, where to do it is the issue. I have command buttons on my form
that controls whether the list box is visible, and I do it in the Click event
of the command button. You may want to try using the Lost Focus event of the
first list box. You will also want the test your code and see if you are
getting the correct results from the code that builds the where condition.

"Annemarie" wrote:

I added the buildwherecondition to my form...
my second list box already had a record source like follows..

SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] WHERE ((([bfr
data].sa) Like forms![search criteria test]!txtfiltersa));

Now that i have changed it to the multi select it does not pull any
records for the second list box.

I have tried changing your record source codes around and adding them
in after update, before update, etc and I must just be missing
something. any suggestions?

I might be a lost cause....



Klatuu wrote:
Ignore the printing what is selected. That is only an example. The
important part is how you iterate through the collection. You wont find that
property in the Properties Dialog. It is available only in VBA. Here is an
example:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

It passes back a string that will later be used to construct the Where
condition.

What I am doing is a little more complex that what you need, but as an
example, here is where I actually construt the row source for the list box
based on the selections made in the previous list box.

With Me.lstActivity
strWhere = FindWhere(4)
If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT Activity " & _
"FROM quniUPOLists " & _
strWhere & _
"ORDER BY Activity;"
End If
If .ListCount = 0 Then
Me.lblActivity.Caption = "No Matches"
Me.lblActivity.ForeColor = 255
.Height = 0
Me.cmdBillNetwork.SetFocus
Else
.Height = 3015
.SetFocus
End If
End With
DoCmd.Hourglass False


"Annemarie" wrote:

I understand the concept I just cant quite figure out the actual doing
it... First i cannot find the itemsSelected property...and I am not
very experienced with coding.

I looked through the help section for ItemsSelected but the examples
were only for printing based on what was selected.

Sorry I am pretty lost on this.......

VBA keeps debugging it and when it comes back the txtfilterSA is
highlighted under the strWhere (third line in sub)...

what am i doing wrong? probably several things...is this slavagable?
Again...sorry I am REALLY not experienced with coding..just learning on
my own as I go...

Here is my code..
Private Sub Label49_Click()
With Me.txtfilterCCN
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] "
strWhere = WHERE(([bfr data].SA) Like Forms![search criteria
for Report Preview]![txtfilterSA])

strSQL = strSQL & strWhere

If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT CCN " & _
"FROM [bfr data].CCN " & _
strWhere & _
"ORDER BY [bfr data].CCN;"
End If
End With
DoCmd.Hourglass False
End Sub


Klatuu wrote:
The trick is to start with your SELECT statement without a Where condition:

strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] "

Then, once the Where part has been created

strSQL = strSQL & strWhere

Of course, where to do it is the issue. I have command buttons on my form
that controls whether the list box is visible, and I do it in the Click event
of the command button. You may want to try using the Lost Focus event of the
first list box. You will also want the test your code and see if you are
getting the correct results from the code that builds the where condition.

"Annemarie" wrote:

I added the buildwherecondition to my form...
my second list box already had a record source like follows..

SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] WHERE ((([bfr
data].sa) Like forms![search criteria test]!txtfiltersa));

Now that i have changed it to the multi select it does not pull any
records for the second list box.

I have tried changing your record source codes around and adding them
in after update, before update, etc and I must just be missing
something. any suggestions?

I might be a lost cause....



Klatuu wrote:
Ignore the printing what is selected. That is only an example. The
important part is how you iterate through the collection. You wont find that
property in the Properties Dialog. It is available only in VBA. Here is an
example:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

It passes back a string that will later be used to construct the Where
condition.

What I am doing is a little more complex that what you need, but as an
example, here is where I actually construt the row source for the list box
based on the selections made in the previous list box.

With Me.lstActivity
strWhere = FindWhere(4)
If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT Activity " & _
"FROM quniUPOLists " & _
strWhere & _
"ORDER BY Activity;"
End If
If .ListCount = 0 Then
Me.lblActivity.Caption = "No Matches"
Me.lblActivity.ForeColor = 255
.Height = 0
Me.cmdBillNetwork.SetFocus
Else
.Height = 3015
.SetFocus
End If
End With
DoCmd.Hourglass False


"Annemarie" wrote:

I understand the concept I just cant quite figure out the actual doing
it... First i cannot find the itemsSelected property...and I am not
very experienced with coding.

I looked through the help section for ItemsSelected but the examples
were only for printing based on what was selected.

Sorry I am pretty lost on this.......

You need qoutes around the strwhere line:
strWhere = "WHERE(([bfr data].SA) Like Forms![search criteria
for Report Preview]![txtfilterSA])"

Are you wanting to use the above Where criteria and what is in the list box
as well?


"Annemarie" wrote:

VBA keeps debugging it and when it comes back the txtfilterSA is
highlighted under the strWhere (third line in sub)...

what am i doing wrong? probably several things...is this slavagable?
Again...sorry I am REALLY not experienced with coding..just learning on
my own as I go...

Here is my code..
Private Sub Label49_Click()
With Me.txtfilterCCN
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] "
strWhere = WHERE(([bfr data].SA) Like Forms![search criteria
for Report Preview]![txtfilterSA])

strSQL = strSQL & strWhere

If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT CCN " & _
"FROM [bfr data].CCN " & _
strWhere & _
"ORDER BY [bfr data].CCN;"
End If
End With
DoCmd.Hourglass False
End Sub


Klatuu wrote:
The trick is to start with your SELECT statement without a Where condition:

strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] "

Then, once the Where part has been created

strSQL = strSQL & strWhere

Of course, where to do it is the issue. I have command buttons on my form
that controls whether the list box is visible, and I do it in the Click event
of the command button. You may want to try using the Lost Focus event of the
first list box. You will also want the test your code and see if you are
getting the correct results from the code that builds the where condition.

"Annemarie" wrote:

I added the buildwherecondition to my form...
my second list box already had a record source like follows..

SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] WHERE ((([bfr
data].sa) Like forms![search criteria test]!txtfiltersa));

Now that i have changed it to the multi select it does not pull any
records for the second list box.

I have tried changing your record source codes around and adding them
in after update, before update, etc and I must just be missing
something. any suggestions?

I might be a lost cause....



Klatuu wrote:
Ignore the printing what is selected. That is only an example. The
important part is how you iterate through the collection. You wont find that
property in the Properties Dialog. It is available only in VBA. Here is an
example:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

It passes back a string that will later be used to construct the Where
condition.

What I am doing is a little more complex that what you need, but as an
example, here is where I actually construt the row source for the list box
based on the selections made in the previous list box.

With Me.lstActivity
strWhere = FindWhere(4)
If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT Activity " & _
"FROM quniUPOLists " & _
strWhere & _
"ORDER BY Activity;"
End If
If .ListCount = 0 Then
Me.lblActivity.Caption = "No Matches"
Me.lblActivity.ForeColor = 255
.Height = 0
Me.cmdBillNetwork.SetFocus
Else
.Height = 3015
.SetFocus
End If
End With
DoCmd.Hourglass False


"Annemarie" wrote:

I understand the concept I just cant quite figure out the actual doing
it... First i cannot find the itemsSelected property...and I am not
very experienced with coding.

I looked through the help section for ItemsSelected but the examples
were only for printing based on what was selected.

Sorry I am pretty lost on this.......

I want my second list box to populate based on my frist list box which
is a multi select. I dont know if that involves the where criteria,
but that criteria works for the single select list box to pull the
results i needed. I took out the record source for the second list box
when i used the strWhere and strSQL in the code since it was referenced
there.

I have no more debugging with the quotes in there, thanks! but still
nothing appears in my second list box (the CCN one) when I try to
select anything in the first (SA).

I guess im just finding these little problems (like the quotes) because
i dont understand the big problem (that nothing pulls from the multi
select) which you tried to fix earlier.

Mainly now i am just completely lost...I wouldnt be offended if you
give up on me haha...


Klatuu wrote:
You need qoutes around the strwhere line:
strWhere = "WHERE(([bfr data].SA) Like Forms![search criteria
for Report Preview]![txtfilterSA])"

Are you wanting to use the above Where criteria and what is in the list box
as well?


"Annemarie" wrote:

VBA keeps debugging it and when it comes back the txtfilterSA is
highlighted under the strWhere (third line in sub)...

what am i doing wrong? probably several things...is this slavagable?
Again...sorry I am REALLY not experienced with coding..just learning on
my own as I go...

Here is my code..
Private Sub Label49_Click()
With Me.txtfilterCCN
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] "
strWhere = WHERE(([bfr data].SA) Like Forms![search criteria
for Report Preview]![txtfilterSA])

strSQL = strSQL & strWhere

If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT CCN " & _
"FROM [bfr data].CCN " & _
strWhere & _
"ORDER BY [bfr data].CCN;"
End If
End With
DoCmd.Hourglass False
End Sub


Klatuu wrote:
The trick is to start with your SELECT statement without a Where condition:

strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] "

Then, once the Where part has been created

strSQL = strSQL & strWhere

Of course, where to do it is the issue. I have command buttons on my form
that controls whether the list box is visible, and I do it in the Click event
of the command button. You may want to try using the Lost Focus event of the
first list box. You will also want the test your code and see if you are
getting the correct results from the code that builds the where condition.

"Annemarie" wrote:

I added the buildwherecondition to my form...
my second list box already had a record source like follows..

SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] WHERE ((([bfr
data].sa) Like forms![search criteria test]!txtfiltersa));

Now that i have changed it to the multi select it does not pull any
records for the second list box.

I have tried changing your record source codes around and adding them
in after update, before update, etc and I must just be missing
something. any suggestions?

I might be a lost cause....



Klatuu wrote:
Ignore the printing what is selected. That is only an example. The
important part is how you iterate through the collection. You wont find that
property in the Properties Dialog. It is available only in VBA. Here is an
example:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

It passes back a string that will later be used to construct the Where
condition.

What I am doing is a little more complex that what you need, but as an
example, here is where I actually construt the row source for the list box
based on the selections made in the previous list box.

With Me.lstActivity
strWhere = FindWhere(4)
If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT Activity " & _
"FROM quniUPOLists " & _
strWhere & _
"ORDER BY Activity;"
End If
If .ListCount = 0 Then
Me.lblActivity.Caption = "No Matches"
Me.lblActivity.ForeColor = 255
.Height = 0
Me.cmdBillNetwork.SetFocus
Else
.Height = 3015
.SetFocus
End If
End With
DoCmd.Hourglass False


"Annemarie" wrote:

I understand the concept I just cant quite figure out the actual doing
it... First i cannot find the itemsSelected property...and I am not
very experienced with coding.

I looked through the help section for ItemsSelected but the examples
were only for printing based on what was selected.

Sorry I am pretty lost on this.......

If the Where condtion we fixed is to filter the first list box, what we want
to do is filter based on what is selected in the first list box and any other
filtering that is necessary. Post back with what the filtering rules are for
the second list box and we will go from there.

"Annemarie" wrote:

I want my second list box to populate based on my frist list box which
is a multi select. I dont know if that involves the where criteria,
but that criteria works for the single select list box to pull the
results i needed. I took out the record source for the second list box
when i used the strWhere and strSQL in the code since it was referenced
there.

I have no more debugging with the quotes in there, thanks! but still
nothing appears in my second list box (the CCN one) when I try to
select anything in the first (SA).

I guess im just finding these little problems (like the quotes) because
i dont understand the big problem (that nothing pulls from the multi
select) which you tried to fix earlier.

Mainly now i am just completely lost...I wouldnt be offended if you
give up on me haha...


Klatuu wrote:
You need qoutes around the strwhere line:
strWhere = "WHERE(([bfr data].SA) Like Forms![search criteria
for Report Preview]![txtfilterSA])"

Are you wanting to use the above Where criteria and what is in the list box
as well?


"Annemarie" wrote:

VBA keeps debugging it and when it comes back the txtfilterSA is
highlighted under the strWhere (third line in sub)...

what am i doing wrong? probably several things...is this slavagable?
Again...sorry I am REALLY not experienced with coding..just learning on
my own as I go...

Here is my code..
Private Sub Label49_Click()
With Me.txtfilterCCN
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] "
strWhere = WHERE(([bfr data].SA) Like Forms![search criteria
for Report Preview]![txtfilterSA])

strSQL = strSQL & strWhere

If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT CCN " & _
"FROM [bfr data].CCN " & _
strWhere & _
"ORDER BY [bfr data].CCN;"
End If
End With
DoCmd.Hourglass False
End Sub


Klatuu wrote:
The trick is to start with your SELECT statement without a Where condition:

strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] "

Then, once the Where part has been created

strSQL = strSQL & strWhere

Of course, where to do it is the issue. I have command buttons on my form
that controls whether the list box is visible, and I do it in the Click event
of the command button. You may want to try using the Lost Focus event of the
first list box. You will also want the test your code and see if you are
getting the correct results from the code that builds the where condition.

"Annemarie" wrote:

I added the buildwherecondition to my form...
my second list box already had a record source like follows..

SELECT DISTINCTROW [bfr data].ccn FROM [bfr data] WHERE ((([bfr
data].sa) Like forms![search criteria test]!txtfiltersa));

Now that i have changed it to the multi select it does not pull any
records for the second list box.

I have tried changing your record source codes around and adding them
in after update, before update, etc and I must just be missing
something. any suggestions?

I might be a lost cause....



Klatuu wrote:
Ignore the printing what is selected. That is only an example. The
important part is how you iterate through the collection. You wont find that
property in the Properties Dialog. It is available only in VBA. Here is an
example:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

It passes back a string that will later be used to construct the Where
condition.

What I am doing is a little more complex that what you need, but as an
example, here is where I actually construt the row source for the list box
based on the selections made in the previous list box.

With Me.lstActivity
strWhere = FindWhere(4)
If Len(strWhere) 0 Then
strWhere = "WHERE " & strWhere
End If
If .RowSource = "" Then
.RowSource = "SELECT DISTINCT Activity " & _
"FROM quniUPOLists " & _
strWhere & _
"ORDER BY Activity;"
End If
If .ListCount = 0 Then
Me.lblActivity.Caption = "No Matches"
Me.lblActivity.ForeColor = 255
.Height = 0
Me.cmdBillNetwork.SetFocus
Else
.Height = 3015
.SetFocus
End If
End With
DoCmd.Hourglass False


"Annemarie" wrote:

I understand the concept I just cant quite figure out the actual doing
it... First i cannot find the itemsSelected property...and I am not
very experienced with coding.

I looked through the help section for ItemsSelected but the examples
were only for printing based on what was selected.

Sorry I am pretty lost on this.......