Combo box with sorted query will not return all records

[Barry]

I have an Access 2007 project connected to a SQL 2003 database. One query
returns 71,074 records whether sorted or unsorted. (Max records = 0.) When
using this query as the rowsource for a combo box on a form the drop down
only returns a portion of the records IF the query is sorted by one field.
If the query is NOT sorted, it returns all the records in the drop down. How
can I sort the records and have access to all. Thanks, Barry

 

[KARL DEWEY]

Post the SQL of your sorted query.

 

[Barry]

Here is my query. Seems pretty simple to me.

SELECT TOP 100 PERCENT item_id, item_desc
FROM CommerceCenter.dbo.inv_mast WITH (NOLOCK)

Thanks, Barry

 

[Barry]

The sorted query syntax.

SELECT TOP 100 PERCENT item_id, item_desc
FROM CommerceCenter.dbo.inv_mast WITH (NOLOCK)
ORDER BY item_

 

[KARL DEWEY]

Either it is a typo or your error is that the sort statement is incomplete --
item_id VS item_

 

[Barry]

Incomplete copy/paste. Actual is:

SELECT TOP 100 PERCENT item_id, item_desc
FROM CommerceCenter.dbo.inv_mast WITH (NOLOCK)
ORDER BY item_id

Barry

 

[KARL DEWEY]

I donno but try dropping 'TOP 100 PERCENT' from the statement.

 

[KARL DEWEY]

Another thing to try is to remove the sort from the query and put it in the
rowsource for the combo box.
Instead of your query name like CommerceCenterList enter --
SELECT item_id, item_desc FROM CommerceCenterList ORDER BY item_id;