If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Combo box populated with column data and a string?

I have a quick question, that I'm not really sure how to search for on the net.

I have a combo box, that's populated with:

Code:

SELECT DISTINCT [Reason]
FROM main_customer_history

Simple enough...

Now what I want, is for it to populate the combo box with that data, plus the string, 'Other'.

So the combo box ends up showing something like:

Code:

Hire
Repair
Replacement
Other

I can't imagine it's difficult, I just don't know what keyword I should be searching for on good ol' Google.

Thanks for any assistance, as ever guys.

PS: I realise I could set it as a value list, instead of a table/query list, then just dump all the distinct items from the [Reason] field into a string, and simply add the word 'Other' to it.

I was hoping though that there was maybe a way to make a union table (or something similar) between the [Reason] field data and the string 'Other'. Then I could call this union table as the rowsource... Purely speculation though, there could well be a much simpler way to achieve this..?

Should give you what you want. What are you going to do with records when the user selects 'Other', just out of interest?

The combobox will be populated with all currently used reasons for a returned product.

The 'Other', that I'm adding, if selected, will enable a textbox adjacent to the combobox, that the user can type in a new reason.

This will then be saved to the record, along with all the other bits and bobs that are filled in on the form, and then obviously in the future the 'new' reason will appear in the combobox.

It's for a historical record of customers products that have been returned for various reasons (as you asked )...

I'll try this out shortly (lunch getting in the way), but do you know if I put the string first, followed by the union, and then the field lookup, will that make 'Other' appear at the top of the list, followed by everything else..?

The presence of DISTINCT will force an ascending sort, AFAIR. It doesn't matter which way around you order the SELECT statement, although you will need a FROM clause before the UNION.

However, you might be better served by setting the LimitToList property of the combo box to No, and investigating the OnNotInList event. See the Help file for more details, but in essence this will allow you to run code by exception to add a new value to a table (for example). If the combo box is populated from the same table as the parent form in updating, you don't need to code anything here - just let the users add the new value, and the form will add it to the table (I assume).

10% of magic is knowing something that no-one else does. The rest is misdirection.

Aye, I see where you're coming from, and in any other situation I'd probably take that approach.

Unfortunately though, the people that will be using this have clocked up about 20 hours of PC usage in their lifetime, combined. So in the interest of making things as simple and obvious as possible, I think selecting 'Other', then a box either enabling or becoming visible next to it will fit the specification better.

Otherwise I'll be getting constantly asked, "How am I supposed to say that it was a 'Suspected Fake', when there isn't an option for it!?", etc.