I want a simple multi-select list box to begin with "ALL" followed by the specified records of the subset of a Table.

Instead, the following row source returns all the table records repeating "ALL" many times



I thought the trouble might be using XCatID as the subset (it is a FK not the PK), so I tried with a Query named XUpTPlantTyQ having the following sql


And wrote the row source as:

Worked better as only the subset records are displayed, but "All" is repeated before each record rather than just once at the beginning of the list.

Table Structure
XUpT


Can anyone suggest where I'm going wrong (IMG:style_emoticons/default/iconfused.gif)

Hi Harry,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

What happens if you add the TOP keyword to your first query? For example:

SELECT TOP 1 ...
UNION
SELECT...

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

As DB says, you will need a TOP 1. I also like to use MSysObjects to insure that I always get at least one record. Plus, you will likely need to (or rather 'should') include literal values that are of the same datatype for XCatID and SortOrder.

Thanks Db & Brent!!

(version is 2007 - (IMG:style_emoticons/default/dunce.gif) )

The key seems to be "you will likely need to (or rather 'should') include literal values that are of the same datatype for XCatID and SortOrder".

The query works with TOP 1, but omitting it does not seem to change the resulting list.

Brent, I've seen you write other UNION queries with (what I would call) the single record placed second. It seem to work the way I have it too. What are the advantages to doing it your way?

For anyone follow this, the final formulation I use is:

Hi Harry,

Glad to hear you got it to work. Since you are using the same data source, the TOP keyword is not necessary unless you are using UNION ALL, but it is necessary when using Brent's approach of using the MSysObjects table for the other source. I'll let him explain the difference between your structure and his.

Good luck with your project.

>> Brent, I've seen you write other UNION queries with (what I would call) the single record placed second. It seem to work the way I have it too. What are the advantages to doing it your way? <<

... Umm ... I like to do it that way (IMG:style_emoticons/default/smile.gif) (IMG:style_emoticons/default/laugh.gif)

Actually, I do have a reason that I do it --- With a UNION query, the datatypes for each column are determined by the first SELECT statement, if a column has mixed types after UNION records come into the mix, the Text datatype will be used. So to me, I like to have the datatypes "lead" by my actual data, and then "fill" in with my "(All)" row. Also, I like to see where the data for my data is sourced right up front (ie: the first SELECT clause) for readability (at least for me).

----

Also, with out the TOP 1, EVERY record from XUpT is returned (so you have lots of rows with literal values for the "(All)" row), then the UNION operator applies a DISTINCT in order to suppress the duplicate rows and give the impression that only one row was returned. So, the TOP 1 is included in the SQL Statement to relieve some burden on the dbengine.

I use MSysObjects for my "(all)" row because using an object like XUpT as the vehicle to get the "(All)" row is that the db engine has to spend some effort (ie: time) working with the Table object to SELECT the data (and more time is consumed if your table objects are linked table objects because you are consuming network band width) --- all to get values that don't even exist in the defined Table object. Plus, if XUpT does NOT return any records, list box (or combo box) will not show an "(All)" row.

So, in order to avoid the pitfalls mentioned with regards to using a user defined object (ie: a Query object or Table object), I use the MSysObjects table object. The MSysObjects table is a local table object that can be queried VERY quickly. It is maintained by the database engine and the Access UI so I know it will always be there (for MDB's and ACCDB's) and will always return at least one record.

{hey db ... this was like deja vu! ... we just did a thread like this not too long ago right?}

.. By the way, if this is a Multi-Select list box, do you have an event defined to UN-Select your selections when the "(All)" choice is made?

Hi Brent,


I do seem to remember something similar before; but since it's your technique, I thought it would be best to let you explain it. (IMG:style_emoticons/default/grin.gif)

Thanks! (IMG:style_emoticons/default/thumbup.gif)

Thanks Brent! Very helpful explanation.

And yup, have some code to handle the all click - written by some guy BSpaulding (any relation, lol)

Here it is again for anyone needing it: