From: Access on
I have an unbound combo box linked to a query that sorts on LastName
and FirstName. It works when the form is loaded but when I reset the
rowsource of the combo box in code the combo box displays the correct
list of names, but they are not sorted alphabetically

Here is the code
Me.cmbLookupName.RowSource =SELECT qlkpPeople.* FROM qlkpPeople WHERE
(((qlkpPeople.PeopleID) In (select peopleid from qselstudents))) AND
(PeopleID in (SELECT PeopleID FROM qselFilteredPeopleAffiliates))
ORDER BY LastName, FirstName;
Me.cmbLookupName.Requery

Any ideas of why this doesn't work? I threw in the "ORDER BY" even
though the query qlkpPeople is already sorted on LastName and FirstName
From: Salad on
Access wrote:

> I have an unbound combo box linked to a query that sorts on LastName
> and FirstName. It works when the form is loaded but when I reset the
> rowsource of the combo box in code the combo box displays the correct
> list of names, but they are not sorted alphabetically

When you say "It works" do you mean that is the rowsource when the form
is loaded? Is so, why are you resetting the combobox's rowsource?
>
> Here is the code
> Me.cmbLookupName.RowSource =SELECT qlkpPeople.* FROM qlkpPeople WHERE
> (((qlkpPeople.PeopleID) In (select peopleid from qselstudents))) AND
> (PeopleID in (SELECT PeopleID FROM qselFilteredPeopleAffiliates))
> ORDER BY LastName, FirstName;

If for some odd reason it does not work have you copied the SQL
statement into a query? And then run the query?

What happens if you place your two subselects in their own queries and
creating a new query that links to those two and running it?

What happens if you just run qlkpPeople?

> Me.cmbLookupName.Requery
>
> Any ideas of why this doesn't work?

One possibility is that the query isn't correct. Ain't debugging fun?

I threw in the "ORDER BY" even
> though the query qlkpPeople is already sorted on LastName and FirstName
From: Marshall Barton on
Access wrote:

>I have an unbound combo box linked to a query that sorts on LastName
>and FirstName. It works when the form is loaded but when I reset the
>rowsource of the combo box in code the combo box displays the correct
>list of names, but they are not sorted alphabetically
>
>Here is the code
>Me.cmbLookupName.RowSource =SELECT qlkpPeople.* FROM qlkpPeople WHERE
>(((qlkpPeople.PeopleID) In (select peopleid from qselstudents))) AND
>(PeopleID in (SELECT PeopleID FROM qselFilteredPeopleAffiliates))
>ORDER BY LastName, FirstName;
>Me.cmbLookupName.Requery
>

If that really is the code to set the RowSource, it is
invalid. The SQL must be in Quotes.

Me.cmbLookupName.RowSource = "SELECT qlkpPeople.* " _
& "FROM qlkpPeople " _
& "WHERE qlkpPeople.PeopleID In(select
selstudents.peopleid from selstudents) " _
& "AND qlkpPeople.PeopleID in (SELECT
qselFilteredPeopleAffiliates.PeopleID FROM
qselFilteredPeopleAffiliates) " _
& "ORDER BY LastName, FirstName"

The Requery is redundant and a waste of resources.

--
Marsh
From: Access on
On Jul 4, 4:11 pm, Marshall Barton <marshbar...(a)wowway.com> wrote:
> Access wrote:
> >I have an unbound combo box linked to a query that sorts on LastName
> >and FirstName.  It works when the form is loaded but when I reset the
> >rowsource of the combo box in code the combo box displays the correct
> >list of names, but they are not sorted alphabetically
>
> >Here is the code
> >Me.cmbLookupName.RowSource =SELECT qlkpPeople.* FROM qlkpPeople WHERE
> >(((qlkpPeople.PeopleID) In (select peopleid from qselstudents))) AND
> >(PeopleID in (SELECT PeopleID FROM qselFilteredPeopleAffiliates))
> >ORDER BY LastName, FirstName;
> >Me.cmbLookupName.Requery
>
> If that really is the code to set the RowSource, it is
> invalid.  The SQL must be in Quotes.
>
> Me.cmbLookupName.RowSource = "SELECT qlkpPeople.* " _
>    & "FROM qlkpPeople " _
>    & "WHERE qlkpPeople.PeopleID In(select
> selstudents.peopleid from selstudents) " _
>          & "AND qlkpPeople.PeopleID in (SELECT
> qselFilteredPeopleAffiliates.PeopleID FROM
> qselFilteredPeopleAffiliates) " _
>    & "ORDER BY LastName, FirstName"
>
> The Requery is redundant and a waste of resources.
>
> --
> Marsh


What a helpful pronouncement. I suppose that explains why it doesn't
work. Thanks alot!
From: Access on
On Jul 4, 3:11 pm, Salad <sa...(a)oilandvinegar.com> wrote:
> Access wrote:
> > I have an unbound combo box linked to a query that sorts on LastName
> > and FirstName.  It works when the form is loaded but when I reset the
> > rowsource of the combo box in code the combo box displays the correct
> > list of names, but they are not sorted alphabetically
>
> When you say "It works" do you mean that is the rowsource when the form
> is loaded?  Is so, why are you resetting the combobox's rowsource?
>
>
>
> > Here is the code
> > Me.cmbLookupName.RowSource =SELECT qlkpPeople.* FROM qlkpPeople WHERE
> > (((qlkpPeople.PeopleID) In (select peopleid from qselstudents))) AND
> > (PeopleID in (SELECT PeopleID FROM qselFilteredPeopleAffiliates))
> > ORDER BY LastName, FirstName;
>
> If for some odd reason it does not work have you copied the SQL
> statement into a query?  And then run the query?
>
> What happens if you place your two subselects in their own queries and
> creating a new query that links to those two and running it?
>
> What happens if you just run qlkpPeople?
>
> > Me.cmbLookupName.Requery
>
> > Any ideas of why this doesn't work?
>
> One possibility is that the query isn't correct.  Ain't debugging fun?
>
>    I threw in the "ORDER BY" even
>
>
>
> > though the query qlkpPeople is already sorted on LastName and FirstName- Hide quoted text -
>
> - Show quoted text -

When I say it works I mean the correct list of people are displayed.
I am resetting the row source because the user has selected two
filters on the form. The two queries referenced in the "IN" sections
of the WHERE clause have parameters based on the selections on the
form. I thought it would run the select statements in the Where
clause return records and then sort them. Forget about that idiot
Marshall Barton's comments maybe he should go work for Microsoft and
make all their customers go away. So my WHERE clause is inefficient
doesn't explain why it returns records but doesn't sort them.

Anyway I'll play with it and not expose myself to anymore of
Marshall's nasty little remarks. I didn't realize this forum had down
graded itself and been taken over by mean little twits.