Prev: Shifting data from columns and removing question mark charactersfrom columns
Next: populating a combobox with values from two tables
From: Access on 4 Jul 2010 13:31 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 4 Jul 2010 15:11 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 4 Jul 2010 16:11 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 5 Jul 2010 12:45 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 5 Jul 2010 12:51
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. |