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

Access wrote:

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

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

Access wrote:

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

On Jul 4, 4:11*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:

What a helpful pronouncement. I suppose that explains why it doesn't
work. Thanks alot!

On Jul 4, 3:11*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
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.

I am sorry you misunderstood Marshall's explanation.

Your code must have been typed in directly as it could not have worked as you
posted it. At a minimum it needed quote marks around the SQL string. Note
that there are no quotes marks in your posting.

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

And if you assign a valid string to a combobox rowsource property, the combo
is automatically requeried, so you do not need to use the requery to get the
list to update (again).

That said, if your actual code looked like the following I can see no reason
for it to fail to order the results for you. Assumptions being that LastName
and FirstName are valid fields and have the expected values in them.

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

One way to trouble shoot this is to copy the SQL string, paste it in a new
query and see if the query works and gives you the desired result.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Access wrote:

Access wrote:
I didn't think Marshall's reply was off the mark. Nor making demeaning
remarks. Sometimes debugging is not fun and can be frustrating.
Sometimes it is best to step away from it, work on another task for a
bit, then come back to attack it again.

You do need to put the SQL string in quotes as he said.

There is no need to requery since you just changed the rowsource as he
said. If your combo query was pointing to the value of a textbox for
example and the value of the textbox changed, you might simply requery
the combo to update the values. IOW, change the rowsource, requery is
not needed because it is doing an implicit requery since the source has
changed. Change the filter value in a text box, requery.

Access <alderran666 (AT) gmail (DOT) com> wrote in
news:516a68e8-bf63-4e0d-b503-ca02eaea4d4f (AT) q12g2000yqj (DOT) googlegroups.co
m:

What a childish reaction! You should count yourself lucky that your
question didn't attracty my attention, since Marshall was not even
rude in his response.

Having tantrums like this is a really good way to lose the interest
of the best experts reading the group -- they'll killfile a whiner
just so they don't have to put up with reading it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Access wrote:

My apparently obscure point is that we can not help you
debug a query without seeing the query as it was used. What
you posted was not debuggable without making assumptions
about the real code you have. Because the SQL as you posted
apparently has nothing wrong with it, we could have been
discussing something different than the real query. All
this wasted back and forth can be avoided if you post a
Copy/Paste of the actual code that did not produce the
expected redults.

If your code generated an equivalent query that would do
what you said when copy/pasted from the combo box's row
source to a new query's SQL view and executed directly from
there, then the next step would be to analyze the query the
source of your posted query. If that doesn't come up with
what caused your problem, then posting a Copy/Paste of that
query's code/SQL along with a small sample of the data might
help us help you figure out the problem.

--
Marsh