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" wrote:
> 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" wrote:
>
> >
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > >
> > > > I assume that Combo2 is being populated correctly. Can you please
post the
> > > > rowsources for Combo2 and your listbox?
> > > >
> > > > Thanks.
> > > >
> > > >
> > > > "Walter" wrote:
> > > >
> > > > > 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?
> > > > > --
> > > > > Thanks for your help!
> > > > > Walter
|
