Trying to Add an empty space to my combo box...not working right

[Toadums]

Hey. So I looked around on the forum for how to do this and came across a link to this page:

http://www.mvps.org/access/forms/frm0043.htm (i use the 'table/query' option)

and So I copied that, and it works great for one of my combo boxes:

SELECT [tblProjects1].[ProjectID], [tblProjects1].[ProjectName] FROM tblProjects1 UNION Select Null as AllChoice , "(All)" as Bogus From tblProjects1 ORDER BY [tblProjects1].[ProjectID];

but when I tried it on my other combo box, the results of my combo box are:

All
All
All
All
etc...
Paul
Nanine
Ed
Pat
etc..

(there are 9 names, and the word "All" appears 9 times aswell, so the combo box has 18 things in it..)

not sure why it is doing this for this one ONLY, since it works fine without the All option, and the code controlling the all option is the same as the combo box above.

here is the code i used:

SELECT [tblUsers1].[user_id], [tblUsers1].[FirstName] FROM tblUsers1 UNION Select Null as AllChoice , "(All)" as Bogus From tblUsers1 ORDER BY [tblUsers1].[FirstName];

Anyone know how to make the "All" appear only once? Like I said, works perfect for the first one i pasted in, just shows "All" once..

Thanks!
Paul

 

[RuralGuy]

The OrderBy clause usually specifies the field that is Null so it comes out at the top of the list. Is tblUsers1 a query with a poor Join in it?

 

[Toadums]

I think i found a way around it. I just put SELECT DISTINCT so that only one (All) shows.

thanks tho!

 

[RuralGuy]

That's fine as long as you understand that it is only a kluge.

 

[Toadums]

That's fine as long as you understand that it is only a kluge.

Lol. i have never heard that word before. so i looked it up, and you made me second guess whether or not it is good enough! haha.

Is this way really bad?

 

[RuralGuy]

It depends. The solution you had should have worked and it points to maybe a problem with the RowSource table/query. You can resolve it now or wait and see if it causes an issue down the road.

 

[Toadums]

mm. i will see if it causes any errors down the line

this particular file isnt going out to the masses, just kept between a few of us at work, so i wont worry about it for now!

thanks again

 

[RuralGuy]

Have fun. It will probably be just fine.

 

[smig]

it will cause you a problem if you have another Paul or Ed.

if you already use the UNION you can easily create a table that will include only the "ALL" record (a single record table) and UNION to it.

a more clean option will be to use code to read the values from the table to create a long string, adding the "ALL" (or any other option you want) and populate this string to your combo box.