adding one field to table driven combo combo box

[BBC via AccessMonster.com]

i have combo box whose entries are provided from a table in the database
(rowsource). the combo box is used on multiple forms but in one case i need
to insert another selection as the first one (ie an "n/a"). is there a way
to "insert" at the beginning but still use the table for the rest.

 

[Jeanette Cunningham]

Hi Brian,
you can use a union query.
Select tblCombo.TheField From tblCombo
Union Select "<n/a>" as TheField From tblCombo
OrderBy TheField;

The above will give you a drop down that includes the value <n/a>.
I added the < and > so the n/a would be at the top of the list.

If your combo row source includes a primary key, it would be a better idea
to add the n/a to the table.
Base the row source on a query, and change the where clause to include or
exclude the n/a as needed.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[BBC via AccessMonster.com]

Thanks
Not sure I understand your comment of
I added the < and > so the n/a would be at the top of the list.
Don't see an "and"
But think I understand the rest

The table does have a primary key so it sounds like your last recommendation
would be better
(maybe even more efficient)
thanks, will give it a try

 

[Jeanette Cunningham]

re:
Not sure I understand your comment of

I added the < and > so the n/a would be at the top of the list.
Don't see an "and"
But think I understand the rest

I surrounded the n/a with a less than sign and a greater than sign so that
it would sort at the top of the list. This was for the example using a union
query.
When using a table, you can add an extra field called SortOrder, make it a
number field, Long Integer. Use that to sort the query.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[BBC via AccessMonster.com]

got it, having an age moment I guess.

I'm actually going to use your second suggestion as I already use that sort
order strategy in all my combo box code tables

re:
Not sure I understand your comment of

I added the < and > so the n/a would be at the top of the list.
Don't see an "and"
But think I understand the rest

I surrounded the n/a with a less than sign and a greater than sign so that
it would sort at the top of the list. This was for the example using a union
query.
When using a table, you can add an extra field called SortOrder, make it a
number field, Long Integer. Use that to sort the query.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks
Not sure I understand your comment of

[quoted text clipped - 30 lines]