Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

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

    Hi guys.

    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..?
    Last edited by kez1304; 11-16-11 at 06:51.
    Looking for the perfect beer...

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Code:
    SELECT DISTINCT [Reason]
    FROM main_customer_history
    UNION
    SELECT 'Other'
    Should give you what you want. What are you going to do with records when the user selects 'Other', just out of interest?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by weejas View Post
    Code:
    SELECT DISTINCT [Reason]
    FROM main_customer_history
    UNION
    SELECT 'Other'
    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..?
    Looking for the perfect beer...

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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.

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    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.
    Looking for the perfect beer...

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Ah, yes. Users - the bane of the programmer's life(!)

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

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Most certainly is... Can't wait for the generation gap to close up.
    Looking for the perfect beer...

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by weejas View Post
    Code:
    SELECT DISTINCT [Reason]
    FROM main_customer_history
    UNION
    SELECT 'Other'
    Doesn't seem to work unfortunately. I'm getting an error when trying to click the combobox, saying:

    Query input must contain at least one table or query.
    Which I'm assuming is happening because I'm not specifying where 'Other' is coming from.

    I've tried dumping it in a query instead of just into my code, and the same thing's happening.

    Any more ideas?
    Looking for the perfect beer...

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Simply:
    Code:
    SELECT DISTINCT [Reason]
    FROM main_customer_history
    UNION
    SELECT 'Other'
    FROM main_customer_history
    Have a nice day!

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Beaten to the punch!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Works like a charm.

    Thanks very much guys!
    Looking for the perfect beer...

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •