Row Source, Select all items

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

Hello.

I have the following SQL in my row source field:

SELECT [ALL].[Item] FROM [ALL] WHERE ((([ALL].[Inv_Org])=
[Forms]![EODATA]![Org])) ORDER BY [ALL].[Item];

The field ALL.Inv_Org has 10 options (F01,F02,F03,etc).

The table ALL has 100,000 items, each associated with an
org. If I select F01 in my first combo box (named EODATA!
Org), in my EODATA!Item combo box, I get the 20 items that
are associated with F01. This works fine.

I want to be able to not select any org, and have all
100,000 show in my EODATA!Item combo box. What do I need
to add to my row source code to allow all 100,000 items to
show in my combo box.
 
steve said:
Hello.

I have the following SQL in my row source field:

SELECT [ALL].[Item] FROM [ALL] WHERE ((([ALL].[Inv_Org])=
[Forms]![EODATA]![Org])) ORDER BY [ALL].[Item];

The field ALL.Inv_Org has 10 options (F01,F02,F03,etc).

The table ALL has 100,000 items, each associated with an
org. If I select F01 in my first combo box (named EODATA!
Org), in my EODATA!Item combo box, I get the 20 items that
are associated with F01. This works fine.

I want to be able to not select any org, and have all
100,000 show in my EODATA!Item combo box. What do I need
to add to my row source code to allow all 100,000 items to
show in my combo box.
Click to expand...

This would be feasible via a query like

SELECT [ALL].[Item] FROM [ALL]
WHERE ([ALL].[Inv_Org]=[Forms]![EODATA]![Org])
OR ([Forms]![EODATA]![Org] Is Null)
ORDER BY [ALL].[Item];

except that the maximum number of items that can be displayed in a combo
or list box, the last I looked, is 65,535 (or something very close to
that). Practically speaking, though, a combo box with even that many
items in it is unwieldy and very cumbersome to work with. I don't think
any user is going to find it practical to search for particular item in
a list of 100,000. If you want to use the combo box only for validation
and autocompletion of the user's keyboard entry, you can do it with a
text box and a little code. Aside from that option, you're much better
off requiring the user to select an Org first.
 
Back
Top