add a choice to a drop-down box

[salmonella]

I have a drop-down box whose choices are determined by a query. However, I
would like to add 2 other choices that the query would not offer, and I need
to have the box property as limit to list. How can I do this?

thanks

 

[fredg]

I have a drop-down box whose choices are determined by a query. However, I
would like to add 2 other choices that the query would not offer, and I need
to have the box property as limit to list. How can I do this?

thanks

A little bit more specific information would have been helpful.
Normally you would use a Union Query to add a value to the combo
rowsource.

Here is an example of adding the word "All" to the combo box
rowsource.

Change the table and field names to your actual table and field names.

Note that the symbols <> surround the word "All". That is to have the
list sort with the word <All> at the top of the list before any other
"A" listing.

Select YourTable.[FieldName] From YourTable
Union Select "<ALL>" from YourTable Order by [FieldName];

 

[Stockwell43]

Are you sure there is not a table that holds the choices and the query is
just there to sort the choices in alpha order? Creating a Not In List drop
down would allow users to continually add choices to the drop down anytime
one isn't there. Then it may not all be uniform and neat because people can
be sloppy when entering information. Let me know. If you still need the Not
in List feature, I think I have a site that makes it realy easy to create.

 

[salmonella]

No it won't work. I have seen the code I need but forgot how it was done.
The choices in the box are bacterial isolate ID numbers which are generated
from the database. However, I need to add some other choices besides these
that have nothing to do with bacterial id numbers.

thanks

 

[John W. Vinson]

I have a drop-down box whose choices are determined by a query. However, I
would like to add 2 other choices that the query would not offer, and I need
to have the box property as limit to list. How can I do this?

thanks

Base the combo on a UNION query including the additional two values. If you'll
post the current SQL view of the combo's rowsource query and indicate what
needs to be added, someone can give you the syntax for the new query.

 

[John W. Vinson]

No it won't work. I have seen the code I need but forgot how it was done.
The choices in the box are bacterial isolate ID numbers which are generated
from the database. However, I need to add some other choices besides these
that have nothing to do with bacterial id numbers.

thanks

SELECT [bacterial id], [this], [that] FROM sometable
UNION ALL
SELECT " non bacterial id", "something", "something else" FROM sometable
UNION ALL
SELECT " another non id", "stuff", "nonsense" FROM sometable
ORDER BY 1;

 

[salmonella]

Thanks,
The combo box populates from the bacterial 'isolate name' field as follows:
SELECT DISTINCT Isolate.name
FROM Isolate
ORDER BY Isolate.name;

what I would like to do is add to the isolate name choices in the combo box
things like the word "Positive Control" and "Unknown", etc.

thanks

 

[John W. Vinson]

Thanks,
The combo box populates from the bacterial 'isolate name' field as follows:
SELECT DISTINCT Isolate.name
FROM Isolate
ORDER BY Isolate.name;

what I would like to do is add to the isolate name choices in the combo box
things like the word "Positive Control" and "Unknown", etc.

I'd suggest creating another table named, let's say, OtherCultures, with one
text field CultureName the same size as the (badly misnamed, Name is a
reserved word) Name field in Isolate. Base the combo on a query

SELECT Isolate.Name FROM Isolate
UNION
SELECT CultureName FROM OtherCultures;

You don't need the DISTINCT since a UNION query itself removes duplicates.