Combo box with sorted query will not return all records

  • Thread starter Thread starter Barry
  • Start date Start date
B

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
 
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
 
The sorted query syntax.

SELECT TOP 100 PERCENT item_id, item_desc
FROM CommerceCenter.dbo.inv_mast WITH (NOLOCK)
ORDER BY item_
 
Either it is a typo or your error is that the sort statement is incomplete --
item_id VS item_
 
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
 
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;
 
Back
Top