List box not displaying data

W

Walter

I have a search form with 2 combos and 1 list box. Combo 1's rowsource is a
value list. Combo2's rowsource is a query based on combo1's selection. The
list box rowsource is a query based on combo2's selection. It has 7 columns
with #1 column bound. After updating combo2, the list box shows the columns
with no data. If I paste the SQL into a query window, it returns the
records. Why are they not showing up in the list box?
 
S

Steve

I assume that Combo2 is being populated correctly. Can you please post the
rowsources for Combo2 and your listbox?

Thanks.
 
W

Walter

Yes appears to be combo2 is populating correctly.
Here is the code:
Combo1;set rowsource for combo2
Private Sub SearchType_AfterUpdate()

Select Case [SearchType]

Case "Origin State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[OriginState],
[tblLoadDetails].[OriginState]" & _
"FROM [tblLoadDetails];"

Case "Destination State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[DestinationState],
[tblLoadDetails].[DestinationState] " & _
"FROM [tblLoadDetails];"

Case "Customer"
Me.Criteria.RowSource = _
"SELECT DISTINCT [tblCompanies].[ContactID],
[tblCompanies.CompanyName]" & _
"FROM [tblCompanies];"

End Select

Combo2;set rowsource for listbox
Select Case [SearchType]

Case "Origin State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.OriginState)=[me].[Criteria]));"

Case "Destination State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.DestinationState)=[me].[Criteria]));"

Case "Customer"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.ContactID)=[me].[Criteria]));"

End Select
 
S

Steve

Hi Walter,

It's hard to see if you're using two different routines or just one because
you put "Private Sub SearchType_AfterUpdate()" but don't have an "End Sub"
there, so I don't know if you have all of that in one routine or if the
second Select statement is in "Private Sub Criteria_AfterUpdate()".

Make sure you have your second Select statement in "Private Sub
Criteria_AfterUpdate()". Also, you should add the following to the end of
your SearchType_AfterUpdate() routine:

If Criteria.ListCount > 0 Then
Criteria = Criteria.ItemData(0)
Else
Criteria = Null
End If
Call Criteria_AfterUpdate()

This will ensure that if you change your Search Type, your ListBox gets
refreshed accordingly. It'll automatically select the first item in your
Criteria ComboBox.

In your Criteria_AfterUpdate() routine, make sure to handle the fact that
the value of your Criteria ComboBox could now be Null (by presumably removing
the RowSource or making the ListBox invisible).

Please let me know if this helped.

Thanks.

Steve




Walter said:
Yes appears to be combo2 is populating correctly.
Here is the code:
Combo1;set rowsource for combo2
Private Sub SearchType_AfterUpdate()

Select Case [SearchType]

Case "Origin State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[OriginState],
[tblLoadDetails].[OriginState]" & _
"FROM [tblLoadDetails];"

Case "Destination State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[DestinationState],
[tblLoadDetails].[DestinationState] " & _
"FROM [tblLoadDetails];"

Case "Customer"
Me.Criteria.RowSource = _
"SELECT DISTINCT [tblCompanies].[ContactID],
[tblCompanies.CompanyName]" & _
"FROM [tblCompanies];"

End Select

Combo2;set rowsource for listbox
Select Case [SearchType]

Case "Origin State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.OriginState)=[me].[Criteria]));"

Case "Destination State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.DestinationState)=[me].[Criteria]));"

Case "Customer"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.ContactID)=[me].[Criteria]));"

End Select
--
Thanks for your help!
Walter


Steve said:
I assume that Combo2 is being populated correctly. Can you please post the
rowsources for Combo2 and your listbox?

Thanks.
Click to expand...
Click to expand...
 
W

Walter

Yes each routine is in it's own sub. I just didn't include the "end Sub".
I've pasted your code as you suggested with the same results. As far as
nulls are concerned, I don't understand the possibility of that since the
only choices available for the criteria come from records within the data.
--
Thanks for your help!
Walter


Steve said:
Hi Walter,

It's hard to see if you're using two different routines or just one because
you put "Private Sub SearchType_AfterUpdate()" but don't have an "End Sub"
there, so I don't know if you have all of that in one routine or if the
second Select statement is in "Private Sub Criteria_AfterUpdate()".

Make sure you have your second Select statement in "Private Sub
Criteria_AfterUpdate()". Also, you should add the following to the end of
your SearchType_AfterUpdate() routine:

If Criteria.ListCount > 0 Then
Criteria = Criteria.ItemData(0)
Else
Criteria = Null
End If
Call Criteria_AfterUpdate()

This will ensure that if you change your Search Type, your ListBox gets
refreshed accordingly. It'll automatically select the first item in your
Criteria ComboBox.

In your Criteria_AfterUpdate() routine, make sure to handle the fact that
the value of your Criteria ComboBox could now be Null (by presumably removing
the RowSource or making the ListBox invisible).

Please let me know if this helped.

Thanks.

Steve




Walter said:
Yes appears to be combo2 is populating correctly.
Here is the code:
Combo1;set rowsource for combo2
Private Sub SearchType_AfterUpdate()

Select Case [SearchType]

Case "Origin State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[OriginState],
[tblLoadDetails].[OriginState]" & _
"FROM [tblLoadDetails];"

Case "Destination State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[DestinationState],
[tblLoadDetails].[DestinationState] " & _
"FROM [tblLoadDetails];"

Case "Customer"
Me.Criteria.RowSource = _
"SELECT DISTINCT [tblCompanies].[ContactID],
[tblCompanies.CompanyName]" & _
"FROM [tblCompanies];"

End Select

Combo2;set rowsource for listbox
Select Case [SearchType]

Case "Origin State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.OriginState)=[me].[Criteria]));"

Case "Destination State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.DestinationState)=[me].[Criteria]));"

Case "Customer"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.ContactID)=[me].[Criteria]));"

End Select
--
Thanks for your help!
Walter


Steve said:
I assume that Combo2 is being populated correctly. Can you please post the
rowsources for Combo2 and your listbox?

Thanks.


:

I have a search form with 2 combos and 1 list box. Combo 1's rowsource is a
value list. Combo2's rowsource is a query based on combo1's selection. The
list box rowsource is a query based on combo2's selection. It has 7 columns
with #1 column bound. After updating combo2, the list box shows the columns
with no data. If I paste the SQL into a query window, it returns the
records. Why are they not showing up in the list box?
Click to expand...
Click to expand...
Click to expand...
 
S

Steve

This is what I meant by the nulls: Your Criteria ComboBox is based on your
SearchType ComboBox. However, just because the Criteria ComboBox RowSource
gets updated when the SearchType changes doesn't mean the actual value of the
Criteria ComboBox gets re-initialized.

As a simple example, let's say that valid values for Criteria are 1 and 2
when SearchType is A and 3 and 4 when SearchType is B.

If you select A from SearchType and view the drop-down list for Criteria,
you'll see 1 and 2. Now let's say you select 2. The ListBox gets updated
accordingly (supposedly - this is what needs fixing). Now let's say you
change SearchType to be B. If you view the drop-down list for Criteria now,
you'll see 3 and 4. However, the "2" that you previously selected is still
the actual value in Criteria 2 until the user (or the VBA code) changes it.

The following lines of code will automatically get rid of the "2" and select
the "3" because the 3 is the first one in the newly refreshed drop-down list.
Then, the call to Criteria_AfterUpdate should, in theory, populate the
ListBox with the appropriate information for 3.

If Criteria.ListCount > 0 Then
Criteria = Criteria.ItemData(0)
Else
Criteria = Null
End If
Call Criteria_AfterUpdate()

The Criteria=Null line is just a precautionary measure in case your
selection for SearchType doesn't produce any records for your Criteria
RowSource. If that's the case, you'll need to do something about the ListBox
that may have already been displaying other information. Of course I don't
have access to your data, so I don't know if it's feasible that a selection
in SearchType could result in the Criteria RowSource producing no records.
However, even if you think this scenario can never happen, it's good practice
to put it in. There's no guarantee that the data can't change in the future,
or perhaps records accidentally get deleted from a table, etc.

Back to your original problem, have you confirmed that your code is actually
executing properly? You can put a breakpoint in the Criteria_AfterUpdate
routine and make sure the code to load the ListBox RowSource is being
executed properly when you change a value in the Criteria ComboBox.

If it is, the only other thing I can think of is that the properties of the
ListBox are incorrect. Please make sure that the Row Source Type is
"Table/Query", the Column Count is 7 and you have 7 Column Widths.


Steve




Walter said:
Yes each routine is in it's own sub. I just didn't include the "end Sub".
I've pasted your code as you suggested with the same results. As far as
nulls are concerned, I don't understand the possibility of that since the
only choices available for the criteria come from records within the data.
--
Thanks for your help!
Walter


Steve said:
Hi Walter,

It's hard to see if you're using two different routines or just one because
you put "Private Sub SearchType_AfterUpdate()" but don't have an "End Sub"
there, so I don't know if you have all of that in one routine or if the
second Select statement is in "Private Sub Criteria_AfterUpdate()".

Make sure you have your second Select statement in "Private Sub
Criteria_AfterUpdate()". Also, you should add the following to the end of
your SearchType_AfterUpdate() routine:

If Criteria.ListCount > 0 Then
Criteria = Criteria.ItemData(0)
Else
Criteria = Null
End If
Call Criteria_AfterUpdate()

This will ensure that if you change your Search Type, your ListBox gets
refreshed accordingly. It'll automatically select the first item in your
Criteria ComboBox.

In your Criteria_AfterUpdate() routine, make sure to handle the fact that
the value of your Criteria ComboBox could now be Null (by presumably removing
the RowSource or making the ListBox invisible).

Please let me know if this helped.

Thanks.

Steve




Walter said:
Yes appears to be combo2 is populating correctly.
Here is the code:
Combo1;set rowsource for combo2
Private Sub SearchType_AfterUpdate()

Select Case [SearchType]

Case "Origin State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[OriginState],
[tblLoadDetails].[OriginState]" & _
"FROM [tblLoadDetails];"

Case "Destination State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[DestinationState],
[tblLoadDetails].[DestinationState] " & _
"FROM [tblLoadDetails];"

Case "Customer"
Me.Criteria.RowSource = _
"SELECT DISTINCT [tblCompanies].[ContactID],
[tblCompanies.CompanyName]" & _
"FROM [tblCompanies];"

End Select

Combo2;set rowsource for listbox
Select Case [SearchType]

Case "Origin State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.OriginState)=[me].[Criteria]));"

Case "Destination State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.DestinationState)=[me].[Criteria]));"

Case "Customer"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.ContactID)=[me].[Criteria]));"

End Select
--
Thanks for your help!
Walter


:


I assume that Combo2 is being populated correctly. Can you please post the
rowsources for Combo2 and your listbox?

Thanks.


:

I have a search form with 2 combos and 1 list box. Combo 1's rowsource is a
value list. Combo2's rowsource is a query based on combo1's selection. The
list box rowsource is a query based on combo2's selection. It has 7 columns
with #1 column bound. After updating combo2, the list box shows the columns
with no data. If I paste the SQL into a query window, it returns the
records. Why are they not showing up in the list box?
Click to expand...
Click to expand...
Click to expand...
 
W

Walter

Sorry its taken so long. I've been trying to find the problem. I created a
new form with a list box and a criteria text box. When I put the SQL into
the list box's rowsource it displays the data correctly. However, when I try
to set the rowsource via code it displays blank.
--
Thanks for your help!
Walter


Steve said:
This is what I meant by the nulls: Your Criteria ComboBox is based on your
SearchType ComboBox. However, just because the Criteria ComboBox RowSource
gets updated when the SearchType changes doesn't mean the actual value of the
Criteria ComboBox gets re-initialized.

As a simple example, let's say that valid values for Criteria are 1 and 2
when SearchType is A and 3 and 4 when SearchType is B.

If you select A from SearchType and view the drop-down list for Criteria,
you'll see 1 and 2. Now let's say you select 2. The ListBox gets updated
accordingly (supposedly - this is what needs fixing). Now let's say you
change SearchType to be B. If you view the drop-down list for Criteria now,
you'll see 3 and 4. However, the "2" that you previously selected is still
the actual value in Criteria 2 until the user (or the VBA code) changes it.

The following lines of code will automatically get rid of the "2" and select
the "3" because the 3 is the first one in the newly refreshed drop-down list.
Then, the call to Criteria_AfterUpdate should, in theory, populate the
ListBox with the appropriate information for 3.

If Criteria.ListCount > 0 Then
Criteria = Criteria.ItemData(0)
Else
Criteria = Null
End If
Call Criteria_AfterUpdate()

The Criteria=Null line is just a precautionary measure in case your
selection for SearchType doesn't produce any records for your Criteria
RowSource. If that's the case, you'll need to do something about the ListBox
that may have already been displaying other information. Of course I don't
have access to your data, so I don't know if it's feasible that a selection
in SearchType could result in the Criteria RowSource producing no records.
However, even if you think this scenario can never happen, it's good practice
to put it in. There's no guarantee that the data can't change in the future,
or perhaps records accidentally get deleted from a table, etc.

Back to your original problem, have you confirmed that your code is actually
executing properly? You can put a breakpoint in the Criteria_AfterUpdate
routine and make sure the code to load the ListBox RowSource is being
executed properly when you change a value in the Criteria ComboBox.

If it is, the only other thing I can think of is that the properties of the
ListBox are incorrect. Please make sure that the Row Source Type is
"Table/Query", the Column Count is 7 and you have 7 Column Widths.


Steve




Walter said:
Yes each routine is in it's own sub. I just didn't include the "end Sub".
I've pasted your code as you suggested with the same results. As far as
nulls are concerned, I don't understand the possibility of that since the
only choices available for the criteria come from records within the data.
--
Thanks for your help!
Walter


Steve said:
Hi Walter,

It's hard to see if you're using two different routines or just one because
you put "Private Sub SearchType_AfterUpdate()" but don't have an "End Sub"
there, so I don't know if you have all of that in one routine or if the
second Select statement is in "Private Sub Criteria_AfterUpdate()".

Make sure you have your second Select statement in "Private Sub
Criteria_AfterUpdate()". Also, you should add the following to the end of
your SearchType_AfterUpdate() routine:

If Criteria.ListCount > 0 Then
Criteria = Criteria.ItemData(0)
Else
Criteria = Null
End If
Call Criteria_AfterUpdate()

This will ensure that if you change your Search Type, your ListBox gets
refreshed accordingly. It'll automatically select the first item in your
Criteria ComboBox.

In your Criteria_AfterUpdate() routine, make sure to handle the fact that
the value of your Criteria ComboBox could now be Null (by presumably removing
the RowSource or making the ListBox invisible).

Please let me know if this helped.

Thanks.

Steve




:

Yes appears to be combo2 is populating correctly.
Here is the code:
Combo1;set rowsource for combo2
Private Sub SearchType_AfterUpdate()

Select Case [SearchType]

Case "Origin State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[OriginState],
[tblLoadDetails].[OriginState]" & _
"FROM [tblLoadDetails];"

Case "Destination State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[DestinationState],
[tblLoadDetails].[DestinationState] " & _
"FROM [tblLoadDetails];"

Case "Customer"
Me.Criteria.RowSource = _
"SELECT DISTINCT [tblCompanies].[ContactID],
[tblCompanies.CompanyName]" & _
"FROM [tblCompanies];"

End Select

Combo2;set rowsource for listbox
Select Case [SearchType]

Case "Origin State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.OriginState)=[me].[Criteria]));"

Case "Destination State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.DestinationState)=[me].[Criteria]));"

Case "Customer"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE (((tblLoadDetails.ContactID)=[me].[Criteria]));"

End Select
--
Thanks for your help!
Walter


:


I assume that Combo2 is being populated correctly. Can you please post the
rowsources for Combo2 and your listbox?

Thanks.


:

I have a search form with 2 combos and 1 list box. Combo 1's rowsource is a
value list. Combo2's rowsource is a query based on combo1's selection. The
list box rowsource is a query based on combo2's selection. It has 7 columns
with #1 column bound. After updating combo2, the list box shows the columns
with no data. If I paste the SQL into a query window, it returns the
records. Why are they not showing up in the list box?
Click to expand...
Click to expand...
Click to expand...
 
R

Rockn

What is the code you are using to populate the text box?


Walter said:
Sorry its taken so long. I've been trying to find the problem. I created
a
new form with a list box and a criteria text box. When I put the SQL into
the list box's rowsource it displays the data correctly. However, when I
try
to set the rowsource via code it displays blank.
--
Thanks for your help!
Walter


Steve said:
This is what I meant by the nulls: Your Criteria ComboBox is based on
your
SearchType ComboBox. However, just because the Criteria ComboBox
RowSource
gets updated when the SearchType changes doesn't mean the actual value of
the
Criteria ComboBox gets re-initialized.

As a simple example, let's say that valid values for Criteria are 1 and 2
when SearchType is A and 3 and 4 when SearchType is B.

If you select A from SearchType and view the drop-down list for Criteria,
you'll see 1 and 2. Now let's say you select 2. The ListBox gets
updated
accordingly (supposedly - this is what needs fixing). Now let's say you
change SearchType to be B. If you view the drop-down list for Criteria
now,
you'll see 3 and 4. However, the "2" that you previously selected is
still
the actual value in Criteria 2 until the user (or the VBA code) changes
it.

The following lines of code will automatically get rid of the "2" and
select
the "3" because the 3 is the first one in the newly refreshed drop-down
list.
Then, the call to Criteria_AfterUpdate should, in theory, populate the
ListBox with the appropriate information for 3.

If Criteria.ListCount > 0 Then
Criteria = Criteria.ItemData(0)
Else
Criteria = Null
End If
Call Criteria_AfterUpdate()

The Criteria=Null line is just a precautionary measure in case your
selection for SearchType doesn't produce any records for your Criteria
RowSource. If that's the case, you'll need to do something about the
ListBox
that may have already been displaying other information. Of course I
don't
have access to your data, so I don't know if it's feasible that a
selection
in SearchType could result in the Criteria RowSource producing no
records.
However, even if you think this scenario can never happen, it's good
practice
to put it in. There's no guarantee that the data can't change in the
future,
or perhaps records accidentally get deleted from a table, etc.

Back to your original problem, have you confirmed that your code is
actually
executing properly? You can put a breakpoint in the Criteria_AfterUpdate
routine and make sure the code to load the ListBox RowSource is being
executed properly when you change a value in the Criteria ComboBox.

If it is, the only other thing I can think of is that the properties of
the
ListBox are incorrect. Please make sure that the Row Source Type is
"Table/Query", the Column Count is 7 and you have 7 Column Widths.


Steve




Walter said:
Yes each routine is in it's own sub. I just didn't include the "end
Sub".
I've pasted your code as you suggested with the same results. As far
as
nulls are concerned, I don't understand the possibility of that since
the
only choices available for the criteria come from records within the
data.
--
Thanks for your help!
Walter


:


Hi Walter,

It's hard to see if you're using two different routines or just one
because
you put "Private Sub SearchType_AfterUpdate()" but don't have an "End
Sub"
there, so I don't know if you have all of that in one routine or if
the
second Select statement is in "Private Sub Criteria_AfterUpdate()".

Make sure you have your second Select statement in "Private Sub
Criteria_AfterUpdate()". Also, you should add the following to the
end of
your SearchType_AfterUpdate() routine:

If Criteria.ListCount > 0 Then
Criteria = Criteria.ItemData(0)
Else
Criteria = Null
End If
Call Criteria_AfterUpdate()

This will ensure that if you change your Search Type, your ListBox
gets
refreshed accordingly. It'll automatically select the first item in
your
Criteria ComboBox.

In your Criteria_AfterUpdate() routine, make sure to handle the fact
that
the value of your Criteria ComboBox could now be Null (by presumably
removing
the RowSource or making the ListBox invisible).

Please let me know if this helped.

Thanks.

Steve




:

Yes appears to be combo2 is populating correctly.
Here is the code:
Combo1;set rowsource for combo2
Private Sub SearchType_AfterUpdate()

Select Case [SearchType]

Case "Origin State"
Me.Criteria.RowSource = _
"SELECT Distinct [tblLoadDetails].[OriginState],
[tblLoadDetails].[OriginState]" & _
"FROM [tblLoadDetails];"

Case "Destination State"
Me.Criteria.RowSource = _
"SELECT Distinct
[tblLoadDetails].[DestinationState],
[tblLoadDetails].[DestinationState] " & _
"FROM [tblLoadDetails];"

Case "Customer"
Me.Criteria.RowSource = _
"SELECT DISTINCT [tblCompanies].[ContactID],
[tblCompanies.CompanyName]" & _
"FROM [tblCompanies];"

End Select

Combo2;set rowsource for listbox
Select Case [SearchType]

Case "Origin State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE
(((tblLoadDetails.OriginState)=[me].[Criteria]));"

Case "Destination State"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE
(((tblLoadDetails.DestinationState)=[me].[Criteria]));"

Case "Customer"
Me.LoadList.RowSource = _
"SELECT tblLoadDetails.LoadDetailsID,
tblLoadDetails.OriginCity," & _
"tblLoadDetails.OriginState,
tblLoadDetails.DestinationCity," & _
"tblLoadDetails.DestinationState,
tblLoadDetails.Product," & _
"tblLoadDetails.HazMat" & _
"FROM tblLoadDetails" & _
"WHERE
(((tblLoadDetails.ContactID)=[me].[Criteria]));"

End Select
--
Thanks for your help!
Walter


:


I assume that Combo2 is being populated correctly. Can you
please post the
rowsources for Combo2 and your listbox?

Thanks.


:

I have a search form with 2 combos and 1 list box. Combo 1's
rowsource is a
value list. Combo2's rowsource is a query based on combo1's
selection. The
list box rowsource is a query based on combo2's selection. It
has 7 columns
with #1 column bound. After updating combo2, the list box
shows the columns
with no data. If I paste the SQL into a query window, it
returns the
records. Why are they not showing up in the list box?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo is null 4
Combi box data entry 2
Combo box trouble 1
Trouble with combo box 1
Combo box trouble 1
Requery Form from Combo Box 3
Combo Box not displaying data. 3
Running a query and setting that value base on another combo box 3

Top