Forms drop-down box does not list all db rows

[Barry]

The query below is set up as an Access view. When I run it from views, it
returns 77,155 records.

SELECT TOP 100 PERCENT CC.dbo.inv_mast.item_id,
CC.dbo.inv_mast.item_desc,
CC.dbo.inv_mast.delete_flag,
dbo.VBH.Item_Id AS VBH_Item_Id,
dbo.VBH.P21_Item_Desc AS VBH_Item_Desc
FROM CC.dbo.inv_mast WITH (NOLOCK) LEFT OUTER JOIN
dbo.VBH ON CC.dbo.inv_mast.item_id = dbo.VBH.Item_Id
WHERE (CC.dbo.inv_mast.delete_flag <> 'Y')

When I use this view as the record source for a drop=down box on a form, it
only returns a small portion of the records (~10%). What could be
restricting the returns on the form? Thank you for your help. Barry

 

[Linq Adams via AccessMonster.com]

Comboboxes do have a limit as to the number of rows they can display, but I
can't lay my finger on the spec right now. If you want all the records
returned where

CC.dbo.inv_mast.delete_flag <> 'Y'

why do you bother to use .

SELECT TOP 100 PERCENT CC.dbo.inv_mast.item_id

instead of simply

SELECT CC.dbo.inv_mast.item_id


Maybe the ***TOP 100 PERCENT*** is confusing Access, and it's interpreting it
as ***TOP10 PERCENT***.

Allen Browne has a hack for comboboxes with large numbers of records that may
be of use to you:

http://allenbrowne.com/ser-32.html

 

[Barry]

The delete_flag qualifier is included as there are about 6,000 "deleted"
records in addition to the 77,155. I do want to filter out those records.

I tried deleting the "TOP 100 PERCENT" from the query, but Access reinserts
it whenver I save the view/query.

Thanks for the hint... I'll check it out.
Barry