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" wrote:
>
> 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
|
