none
Creating a Combo box with display elements that are different from their values. RRS feed

  • Question

  • Hello everyone,
    I have a database field(column) and the control(display control) is a Combo Box.
    I set the row source type to value list and the values are typed in the row source, example: orange;banana;apples.

    Here is what I don't understand: Is it possible to have a Combo box values trigger a different value that is than placed in the database? Example: Lets say the user select Apples from the list or combo box, can this place the value AP in the database?

    If this is possible, where do I place this value, So if the user selects United States, U.S.A is inserted behind the scenes.

    I am trying to have the values in the list work like a label, meaning the user makes a selection but the value that is inserted is different.

    Can this be done?

    Thanks everyone!

    Syn

    Friday, September 30, 2011 2:43 PM

Answers

All replies

  • Hi Syn,

    Better storing your values in a Table, and then use the Table as a RowSource.

    You need to have your data propperly Normalized though.

    That mean that your data for the Country Names for example is stored in a seperate Table called tblCountry, something like this:

    ---------------------------------------------

    tblCountry

    CountryID            AutoNumber/ Long

    Code                   Text

    CountryName      Text

    ----------------------------------------------

    Then in your tblAddress for example you have a Field called CountryID also with Field Type Long.

    Here you store the CountryID chosen from tblCountry.

     

    You transfer this principle to your Form with your Combobox, where you then set the Rowsource to tblCountry.

    Click on the ... to start the Query Builder, then add CountryID, Code, CountryName in your Query Builder.

    Then goto Tab Format, set the following propperties:

    Column Count:    3

    Column Width:    0;2";2"              (This hides the CountryID, but shows the CountryCode and CountryName)

     

    Voila, you have your ComboBox populated with your Code and CountryName you user can choose from.

    Set the ControlSource to the CountryID to the Table where it should be stored, in this example to tblAddress field CountryID.

     

    I hope this make sense, and will help you.

     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Friday, September 30, 2011 8:47 PM
    Moderator
  • Hi Syn,

    Better storing your values in a Table, and then use the Table as a RowSource.

    You need to have your data propperly Normalized though.

    That mean that your data for the Country Names for example is stored in a seperate Table called tblCountry, something like this:

    ---------------------------------------------

    tblCountry

    CountryID            AutoNumber/ Long

    Code                   Text

    CountryName      Text

    ----------------------------------------------

    Then in your tblAddress for example you have a Field called CountryID also with Field Type Long.

    Here you store the CountryID chosen from tblCountry.

     

    You transfer this principle to your Form with your Combobox, where you then set the Rowsource to tblCountry.

    Click on the ... to start the Query Builder, then add CountryID, Code, CountryName in your Query Builder.

    Then goto Tab Format, set the following propperties:

    Column Count:    3

    Column Width:    0;2";2"              (This hides the CountryID, but shows the CountryCode and CountryName)

     

    Voila, you have your ComboBox populated with your Code and CountryName you user can choose from.

    Set the ControlSource to the CountryID to the Table where it should be stored, in this example to tblAddress field CountryID.

     

    I hope this make sense, and will help you.

     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Well, I really appreciate your help and effort but I am more lost and discourage. You have completely lost me.

    This is what I understand from your post.
    1. Create a new table and name tblCountry
    2. Add or Create three fields or column (1) countryID, (2)code and (3)countryName in tblCountry
    3. Make countryID data type AutoNumber and field size Long
    4. Make the code field data type Text, **do I give this field a size?
    5. Make the countryName field data type Text**do I give this field a size?
    6. Add the countryID field to the tblAddress table and set it's field size to Long Integer

    I am confused as to where I place the combo box, is countryID a Primary Key in  tblCounty and a foreign key in the address table?

    Thanks for your help, is there a tutorial some where?

     

    SYN


    • Edited by Synthologic Saturday, October 1, 2011 12:54 AM
    Saturday, October 1, 2011 12:53 AM
  • Hi again,


    I have made a little demo for you, see below link:

    https://skydrive.live.com/redir.aspx?cid=7427c4b2d60cc2c7&resid=7427C4B2D60CC2C7!241

     

    See also more info on how to achieve this in below links:

    http://office.microsoft.com/en-us/access-help/create-a-list-of-choices-by-using-a-list-box-or-combo-box-HA010113052.aspx

    http://allenbrowne.com/ser-27.html

     

    If you have a question, please ask.

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Saturday, October 1, 2011 4:18 AM
    Moderator
  • Hi again,


    I have made a little demo for you, see below link:

    https://skydrive.live.com/redir.aspx?cid=7427c4b2d60cc2c7&resid=7427C4B2D60CC2C7!241

     

    See also more info on how to achieve this in below links:

    http://office.microsoft.com/en-us/access-help/create-a-list-of-choices-by-using-a-list-box-or-combo-box-HA010113052.aspx

    http://allenbrowne.com/ser-27.html

     

    If you have a question, please ask.

     

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"


    Thank you so much for all your help, time and effort. I will look at those posting tomorrow and let you know what has happened.

    Thanks again!

     

    Syn

    Saturday, October 1, 2011 8:55 AM
  • Hi Syn,

    Did you resolved your problem yet?

     

    Thanks,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Monday, October 10, 2011 4:53 PM
    Moderator