Combo Box Sorting Issue
in [Help]
|
Prev: Shifting data from columns and removing question mark charactersfrom columns
Next: populating a combobox with values from two tables
From: John Spencer on 5 Jul 2010 14:20 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: > 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: Salad on 5 Jul 2010 14:31 Access wrote: > 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. 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.
From: David W. Fenton on 5 Jul 2010 16:53 Access <alderran666(a)gmail.com> wrote in news:516a68e8-bf63-4e0d-b503-ca02eaea4d4f(a)q12g2000yqj.googlegroups.co m: > 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. 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/
From: Marshall Barton on 5 Jul 2010 17:29
Access wrote: >On Jul 4, 4:11�pm, Marshall Barton wrote: >> Access wrote: >What a helpful pronouncement. I suppose that explains why it doesn't >work. Thanks alot! 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 |