Sorting Records in a Lookup

[Adam Caramon]

Hi all,

Pretty straight forward question but I can't seem to find the answer anywhere on the net. I have a table with a text field that's values are pulled from another table via the Lookup Wizard. Everything works fine, and I now have a drop-down box that displays all of the fields I have set up with the Lookup Wizard.

But, how do I sort the records that appear in that drop-down? They are not ordered in the same way that the table they are pulled from is, nor do they seem to be ordered based on any one column.

Any help is much appreciated.

 

[neileg]

Table level lookups are more trouble than they are worth. Instead of working at table level, you should use a form and use a combo box based on a query for the lookup. Sort the records however you want in the query.

 

[Moniker]

If you really, really want to use a Lookup Wizard, post exactly what your drop-down's RowSource is. Also, keep in mind that records are stored in tables in no particular order. This is why you use queries to display information in tables and you don't show the tables themselves.

 

[aoswald]

I have a similar problem. I have a combo box with a query as the source but the list does not appear in the same order as the query. How do I get the list in the combo box to be in the same order as the querey?

thanks

 

[Rabbie]

I have a similar problem. I have a combo box with a query as the source but the list does not appear in the same order as the query. How do I get the list in the combo box to be in the same order as the querey?

thanks

Does you query have explicit sorting in it? If so then this should be the order for your combo box.

 

[aoswald]

What do you mean by explicit sorting

 

[Rabbie]

In the data source for your combo does the query have an "Order by" clause in it if you view it in SQL view. If you view it in the query design grid is there anything set in any of the sort fields?
.

 

[aoswald]

in the SQL view of the querey it says "orderbyOnLoad" Yes

 

[Rabbie]

Is Onload the name of a field in your query. I would display the query in Design Grid and select the sort for the field you want the results to be sorted on.

 

[aoswald]

Onload is not a field in the query. I want the results to be sorted by LastName

 

[Rabbie]

Then in the SQL for the source query include "ORDER BY [tablename].[LastName]" in stead of the previous order by statement.

 

[aoswald]

It's not working - the query is sorted correctly but the combo box still has the list out of order

 

[Rabbie]

That doesn't actually make sense. Can you please post the SQL for your query. You are using this query as the source for your Combo aren't you. Which field is it in in the combo properties box.

 

[aoswald]

Here is the SQL:

SELECT Contacts.LastName, Contacts.FirstName, Contacts.File_Number, Contacts.WorkPhone, Contacts.PRCode
FROM Contacts;

yes this query is the source for my combo box: When I hit the combo box drop down, I see the Last and First Name-- OUT OF ORDER

 

[Rabbie]

Your SQL does not have an Order By statement in it. It should be

"SELECT Contacts.LastName, Contacts.FirstName, Contacts.File_Number, Contacts.WorkPhone, Contacts.PRCode
FROM Contacts ORDER BY Contacts.LastName;"

This will ensure the data is sorted in last name order.

The way you have written the query means you will have LastName in the first column, FirstName in the second, etc

 

[aoswald]

OK SQL now looks like this:

SELECT Contacts.LastName, Contacts.FirstName, Contacts.File_Number, Contacts.WorkPhone, Contacts.PRCode
FROM Contacts
ORDER BY Contacts.LastName;

Still not sorted in the combo box by last name.

 

[Rabbie]

Where are you using the query in the combo box.? ie which field in the combo properties is the query in. perhaps you could post your DB so i can see why it is not working?

 

[aoswald]

I have the bound column as 3 which appears to be the column the names are sorted by. If I change the bound column to 1 which is the last name, will that screww up the over 700 records currently in the table?

thanks

 

[aoswald]

ITS WORKING!!!

I was going to make some changes and thought I should back it up first. After I did that it started working

I appreciate your patience

 

[ChEEkY ChiNo]

Hey thanks, this post helped me out a little 2.