Multi Select List Boxes

[Annemarie]

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!

 

[Guest]

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]

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.......

 

[Guest]

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]

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....

 

[Guest]

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.

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....

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]

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

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.

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....

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


:

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.......

 

[Guest]

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?

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

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.

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


:

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.......

 

[Annemarie]

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...

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?

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

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.

:

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


:

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.......

 

[Guest]

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.

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...

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?

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.

:

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


:

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.......

 

[Annemarie]

Ok, my second list box used to have a record source of....

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

Which workes with a single select list box.

and now there is an after update event in the first (multi select)list
box of...(i tried this in onclick for txtfilterCCN list box too)
Private Sub txtfilterSA_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

except nothing happens....
We want specific CCNs to be displayed based on SA's just like in a
single select list box.

 

[Guest]

What you need to do to get list box 2 row source set up is to call the
function I posted earlier that will create the Where Clause based on the
selections made in list box 1. Then you use that to create the row source
for list box 2. Try this. Since I can't test it from here, it may not be
100% accurate, but test it. If you get any errors and can't figure them out,
post back with the error number and the line it occured on and I will help
you through it.

Private Sub txtfilterSA_Click()
Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data]"
strWhere = BuildWhereCondition(Me.ListBox1)
If Len(strWhere) > 0 Then
strWhere = " WHERE " & strWhere
End If
strSQL = strSQL & strWhere
Me.ListBox2.RowSource = strSQL
End Sub

Ok, my second list box used to have a record source of....

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

Which workes with a single select list box.

and now there is an after update event in the first (multi select)list
box of...(i tried this in onclick for txtfilterCCN list box too)
Private Sub txtfilterSA_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

except nothing happens....
We want specific CCNs to be displayed based on SA's just like in a
single select list box.

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]

I put the codes in, and adapted hte BuildWhereCondition codes so that
the Me.ListBox1 matches the Me.ListBox1 in this code...it asks to debug
and highlights this line...

strWhere = BuildWhereCondition(Me.txtfilterSA)

I just placed the BuildWhereCondition codes in vba.....is there a more
specific place i should attach this?

What you need to do to get list box 2 row source set up is to call the
function I posted earlier that will create the Where Clause based on the
selections made in list box 1. Then you use that to create the row source
for list box 2. Try this. Since I can't test it from here, it may not be
100% accurate, but test it. If you get any errors and can't figure them out,
post back with the error number and the line it occured on and I will help
you through it.

Private Sub txtfilterSA_Click()
Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data]"
strWhere = BuildWhereCondition(Me.ListBox1)
If Len(strWhere) > 0 Then
strWhere = " WHERE " & strWhere
End If
strSQL = strSQL & strWhere
Me.ListBox2.RowSource = strSQL
End Sub

Ok, my second list box used to have a record source of....

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

Which workes with a single select list box.

and now there is an after update event in the first (multi select)list
box of...(i tried this in onclick for txtfilterCCN list box too)
Private Sub txtfilterSA_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

except nothing happens....
We want specific CCNs to be displayed based on SA's just like in a
single select list box.

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]

Sorry..

Run-time error 94
Invalid use of null

What you need to do to get list box 2 row source set up is to call the
function I posted earlier that will create the Where Clause based on the
selections made in list box 1. Then you use that to create the row source
for list box 2. Try this. Since I can't test it from here, it may not be
100% accurate, but test it. If you get any errors and can't figure them out,
post back with the error number and the line it occured on and I will help
you through it.

Private Sub txtfilterSA_Click()
Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data]"
strWhere = BuildWhereCondition(Me.ListBox1)
If Len(strWhere) > 0 Then
strWhere = " WHERE " & strWhere
End If
strSQL = strSQL & strWhere
Me.ListBox2.RowSource = strSQL
End Sub

Ok, my second list box used to have a record source of....

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

Which workes with a single select list box.

and now there is an after update event in the first (multi select)list
box of...(i tried this in onclick for txtfilterCCN list box too)
Private Sub txtfilterSA_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

except nothing happens....
We want specific CCNs to be displayed based on SA's just like in a
single select list box.

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.

 

[Guest]

The function should be in the form module general section. That is at the
top of the module right after the Option statements, but before an form or
control related code.

I put the codes in, and adapted hte BuildWhereCondition codes so that
the Me.ListBox1 matches the Me.ListBox1 in this code...it asks to debug
and highlights this line...

strWhere = BuildWhereCondition(Me.txtfilterSA)

I just placed the BuildWhereCondition codes in vba.....is there a more
specific place i should attach this?

What you need to do to get list box 2 row source set up is to call the
function I posted earlier that will create the Where Clause based on the
selections made in list box 1. Then you use that to create the row source
for list box 2. Try this. Since I can't test it from here, it may not be
100% accurate, but test it. If you get any errors and can't figure them out,
post back with the error number and the line it occured on and I will help
you through it.

Private Sub txtfilterSA_Click()
Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data]"
strWhere = BuildWhereCondition(Me.ListBox1)
If Len(strWhere) > 0 Then
strWhere = " WHERE " & strWhere
End If
strSQL = strSQL & strWhere
Me.ListBox2.RowSource = strSQL
End Sub

Ok, my second list box used to have a record source of....

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

Which workes with a single select list box.

and now there is an after update event in the first (multi select)list
box of...(i tried this in onclick for txtfilterCCN list box too)
Private Sub txtfilterSA_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

except nothing happens....
We want specific CCNs to be displayed based on SA's just like in a
single select list box.


Klatuu wrote:
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]

Alright...thats where it is...the highlighted line wiht Me.txtfilterSA
when i place hte cursor over that part says
Me.txtfilterSA=Null....hence the error...it seems that when i select
things in the txtfilterSA field they are not registering therefore
sending Null?

The function should be in the form module general section. That is at the
top of the module right after the Option statements, but before an form or
control related code.

I put the codes in, and adapted hte BuildWhereCondition codes so that
the Me.ListBox1 matches the Me.ListBox1 in this code...it asks to debug
and highlights this line...

strWhere = BuildWhereCondition(Me.txtfilterSA)

I just placed the BuildWhereCondition codes in vba.....is there a more
specific place i should attach this?

What you need to do to get list box 2 row source set up is to call the
function I posted earlier that will create the Where Clause based on the
selections made in list box 1. Then you use that to create the row source
for list box 2. Try this. Since I can't test it from here, it may not be
100% accurate, but test it. If you get any errors and can't figure them out,
post back with the error number and the line it occured on and I will help
you through it.

Private Sub txtfilterSA_Click()
Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data]"
strWhere = BuildWhereCondition(Me.ListBox1)
If Len(strWhere) > 0 Then
strWhere = " WHERE " & strWhere
End If
strSQL = strSQL & strWhere
Me.ListBox2.RowSource = strSQL
End Sub


:

Ok, my second list box used to have a record source of....

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

Which workes with a single select list box.

and now there is an after update event in the first (multi select)list
box of...(i tried this in onclick for txtfilterCCN list box too)
Private Sub txtfilterSA_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

except nothing happens....
We want specific CCNs to be displayed based on SA's just like in a
single select list box.


Klatuu wrote:
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.

 

[Guest]

I'm sorry, I forgot it is the name of the control as a string value you pass
to it.

strWhere = BuildWhereCondition("txtfilterSA")

(just seeing if your paying attention and helping you with your debugging
skills

Alright...thats where it is...the highlighted line wiht Me.txtfilterSA
when i place hte cursor over that part says
Me.txtfilterSA=Null....hence the error...it seems that when i select
things in the txtfilterSA field they are not registering therefore
sending Null?

The function should be in the form module general section. That is at the
top of the module right after the Option statements, but before an form or
control related code.

I put the codes in, and adapted hte BuildWhereCondition codes so that
the Me.ListBox1 matches the Me.ListBox1 in this code...it asks to debug
and highlights this line...

strWhere = BuildWhereCondition(Me.txtfilterSA)

I just placed the BuildWhereCondition codes in vba.....is there a more
specific place i should attach this?




Klatuu wrote:
What you need to do to get list box 2 row source set up is to call the
function I posted earlier that will create the Where Clause based on the
selections made in list box 1. Then you use that to create the row source
for list box 2. Try this. Since I can't test it from here, it may not be
100% accurate, but test it. If you get any errors and can't figure them out,
post back with the error number and the line it occured on and I will help
you through it.

Private Sub txtfilterSA_Click()
Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data]"
strWhere = BuildWhereCondition(Me.ListBox1)
If Len(strWhere) > 0 Then
strWhere = " WHERE " & strWhere
End If
strSQL = strSQL & strWhere
Me.ListBox2.RowSource = strSQL
End Sub


:

Ok, my second list box used to have a record source of....

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

Which workes with a single select list box.

and now there is an after update event in the first (multi select)list
box of...(i tried this in onclick for txtfilterCCN list box too)
Private Sub txtfilterSA_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

except nothing happens....
We want specific CCNs to be displayed based on SA's just like in a
single select list box.


Klatuu wrote:
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]

run-time error 13
Type mismatch

It now hates the line

Set ctl = Me.txtfilterSA(strControl)

from the buildwherecondition.....i hovered over the strControl and it
says strControl="txtfilterSA" but hovering over the me.txtfilterSA
results in it saying Me.TxtfilterSA(strControl)= <type mismatch>

man...this is a beast...seems so simple in my head

I'm sorry, I forgot it is the name of the control as a string value you pass
to it.

strWhere = BuildWhereCondition("txtfilterSA")

(just seeing if your paying attention and helping you with your debugging
skills

Alright...thats where it is...the highlighted line wiht Me.txtfilterSA
when i place hte cursor over that part says
Me.txtfilterSA=Null....hence the error...it seems that when i select
things in the txtfilterSA field they are not registering therefore
sending Null?

The function should be in the form module general section. That is at the
top of the module right after the Option statements, but before an form or
control related code.

:

I put the codes in, and adapted hte BuildWhereCondition codes so that
the Me.ListBox1 matches the Me.ListBox1 in this code...it asks to debug
and highlights this line...

strWhere = BuildWhereCondition(Me.txtfilterSA)

I just placed the BuildWhereCondition codes in vba.....is there a more
specific place i should attach this?




Klatuu wrote:
What you need to do to get list box 2 row source set up is to call the
function I posted earlier that will create the Where Clause based on the
selections made in list box 1. Then you use that to create the row source
for list box 2. Try this. Since I can't test it from here, it may not be
100% accurate, but test it. If you get any errors and can't figure them out,
post back with the error number and the line it occured on and I will help
you through it.

Private Sub txtfilterSA_Click()
Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data]"
strWhere = BuildWhereCondition(Me.ListBox1)
If Len(strWhere) > 0 Then
strWhere = " WHERE " & strWhere
End If
strSQL = strSQL & strWhere
Me.ListBox2.RowSource = strSQL
End Sub


:

Ok, my second list box used to have a record source of....

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

Which workes with a single select list box.

and now there is an after update event in the first (multi select)list
box of...(i tried this in onclick for txtfilterCCN list box too)
Private Sub txtfilterSA_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

except nothing happens....
We want specific CCNs to be displayed based on SA's just like in a
single select list box.


Klatuu wrote:
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.

 

[Guest]

Be sure the top part of the function looks like this:

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)

And that txtfilterSA is the name of the first list box.

run-time error 13
Type mismatch

It now hates the line

Set ctl = Me.txtfilterSA(strControl)

from the buildwherecondition.....i hovered over the strControl and it
says strControl="txtfilterSA" but hovering over the me.txtfilterSA
results in it saying Me.TxtfilterSA(strControl)= <type mismatch>

man...this is a beast...seems so simple in my head

I'm sorry, I forgot it is the name of the control as a string value you pass
to it.

strWhere = BuildWhereCondition("txtfilterSA")

(just seeing if your paying attention and helping you with your debugging
skills

Alright...thats where it is...the highlighted line wiht Me.txtfilterSA
when i place hte cursor over that part says
Me.txtfilterSA=Null....hence the error...it seems that when i select
things in the txtfilterSA field they are not registering therefore
sending Null?


Klatuu wrote:
The function should be in the form module general section. That is at the
top of the module right after the Option statements, but before an form or
control related code.

:

I put the codes in, and adapted hte BuildWhereCondition codes so that
the Me.ListBox1 matches the Me.ListBox1 in this code...it asks to debug
and highlights this line...

strWhere = BuildWhereCondition(Me.txtfilterSA)

I just placed the BuildWhereCondition codes in vba.....is there a more
specific place i should attach this?




Klatuu wrote:
What you need to do to get list box 2 row source set up is to call the
function I posted earlier that will create the Where Clause based on the
selections made in list box 1. Then you use that to create the row source
for list box 2. Try this. Since I can't test it from here, it may not be
100% accurate, but test it. If you get any errors and can't figure them out,
post back with the error number and the line it occured on and I will help
you through it.

Private Sub txtfilterSA_Click()
Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data]"
strWhere = BuildWhereCondition(Me.ListBox1)
If Len(strWhere) > 0 Then
strWhere = " WHERE " & strWhere
End If
strSQL = strSQL & strWhere
Me.ListBox2.RowSource = strSQL
End Sub


:

Ok, my second list box used to have a record source of....

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

Which workes with a single select list box.

and now there is an after update event in the first (multi select)list
box of...(i tried this in onclick for txtfilterCCN list box too)
Private Sub txtfilterSA_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

except nothing happens....
We want specific CCNs to be displayed based on SA's just like in a
single select list box.


Klatuu wrote:
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]

Wooo hooo no errors!

there is data in the second list box, but only when there is nothing
selected in first one, the multi-select list box... when i select
anything it all dissapears

something I missed in this part maybe??

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

Be sure the top part of the function looks like this:

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)

And that txtfilterSA is the name of the first list box.

run-time error 13
Type mismatch

It now hates the line

Set ctl = Me.txtfilterSA(strControl)

from the buildwherecondition.....i hovered over the strControl and it
says strControl="txtfilterSA" but hovering over the me.txtfilterSA
results in it saying Me.TxtfilterSA(strControl)= <type mismatch>

man...this is a beast...seems so simple in my head

I'm sorry, I forgot it is the name of the control as a string value you pass
to it.

strWhere = BuildWhereCondition("txtfilterSA")

(just seeing if your paying attention and helping you with your debugging
skills

:

Alright...thats where it is...the highlighted line wiht Me.txtfilterSA
when i place hte cursor over that part says
Me.txtfilterSA=Null....hence the error...it seems that when i select
things in the txtfilterSA field they are not registering therefore
sending Null?


Klatuu wrote:
The function should be in the form module general section. That is at the
top of the module right after the Option statements, but before an form or
control related code.

:

I put the codes in, and adapted hte BuildWhereCondition codes so that
the Me.ListBox1 matches the Me.ListBox1 in this code...it asks to debug
and highlights this line...

strWhere = BuildWhereCondition(Me.txtfilterSA)

I just placed the BuildWhereCondition codes in vba.....is there a more
specific place i should attach this?




Klatuu wrote:
What you need to do to get list box 2 row source set up is to call the
function I posted earlier that will create the Where Clause based on the
selections made in list box 1. Then you use that to create the row source
for list box 2. Try this. Since I can't test it from here, it may not be
100% accurate, but test it. If you get any errors and can't figure them out,
post back with the error number and the line it occured on and I will help
you through it.

Private Sub txtfilterSA_Click()
Dim strSQL as String
Dim strWhere as String
strSQL = "SELECT DISTINCTROW [bfr data].ccn FROM [bfr data]"
strWhere = BuildWhereCondition(Me.ListBox1)
If Len(strWhere) > 0 Then
strWhere = " WHERE " & strWhere
End If
strSQL = strSQL & strWhere
Me.ListBox2.RowSource = strSQL
End Sub


:

Ok, my second list box used to have a record source of....

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

Which workes with a single select list box.

and now there is an after update event in the first (multi select)list
box of...(i tried this in onclick for txtfilterCCN list box too)
Private Sub txtfilterSA_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

except nothing happens....
We want specific CCNs to be displayed based on SA's just like in a
single select list box.


Klatuu wrote:
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.