Results 1 to 12 of 12
  1. #1
    Join Date
    May 2012
    Posts
    18

    Unanswered: Concatenating Combo boxes

    Can you concatenate the selected value of one combo box with another combo box?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sure, something like this, where TargetControl is where you want to place the concatenated results, and ComboBox1 and ComboBox2 are the Comboboxes:
    Code:
    Private Sub ComboBox1_AfterUpdate()
     If Not IsNull(Me.ComboBox1) And Not IsNull(Me.ComboBox2) Then
       Me.TargetControl = Me.ComboBox1 & " " & Me.ComboBox2
     End If
    End Sub
    
    Private Sub ComboBox2_AfterUpdate()
     If Not IsNull(Me.ComboBox1) And Not IsNull(Me.ComboBox2) Then
       Me.TargetControl = Me.ComboBox1 & " " & Me.ComboBox2
     End If
    End Sub
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2012
    Posts
    18
    Thanks a lot Linq. I will try that and hopefully i can concatenate three combo boxes ^_^

  4. #4
    Join Date
    May 2012
    Posts
    18
    Question though, I tried the code but it concatenates the ID of the values not the values itself.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That means that the ID Field is the Bound Column for the Comboboxes, and the Bound Column is the Value for the given Combo. You may not even know that it was set like this, if you used the Wizard to create the CBOs.

    If the Tables the Comboboxes are based on have Primary Key Fields, such as ID Fields, Access automatically includes them, even if you didn't select them, when creating the Control, and makes them the Bound Column.

    To change the Bound Column, in Form Design View
    • Select the Combobox Control
    • Go to Properties - Data and change the Bound Column to the Column of your choice


    Alternatively, you could change the code used in the concatenation to reference the correct Column like this:
    Code:
    Me.TargetControl = Me.ComboBox1.Column(X) & " " & Me.ComboBox2.Column(X)
    where X is the Column the value you want to concatenate resides. Note that this Column Index is Zero-based, i.e., from Left-to-Right,

    First Column is 0
    Second Column is 1
    Third Column is 2

    and so forth.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    May 2012
    Posts
    18
    Thank you very much. I'm doing it now and that worked ^_^

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    May 2012
    Posts
    18
    Another question, why is that in my table it saves the ID. Can I have access to use which I selected instead of its ID?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you knwo the ID then you can get any other elements from the underlying table for the combo cox
    whether you do that using dlookup or as another query

    the whole ethos behind a relational database is that providing you can associate a piece of information in one table with another table then you can extract whatever you require suign that association

    ferinstance

    lets say you had three tables describing geographical data
    eg
    continents
    countries
    regions

    assuming you know countries, you could find what regions comprise that country, and or you coudl find what continent that country is asbrived to.

    if doing it on a query look at the join clause in a select query
    Google

    as your model develops you could include another table to store cities, defingin what region they belong to (and therefore implicitly what country and continent they are ascribed to.
    equally you coudl add suburbs within cities.


    of course you could also use a self referencing single table whose to level was continent, and bototm level was wht ever you wanted eg:-
    ID
    Description
    ParentID

    -5 Europe
    -6 North America
    -3 Asia
    -7 South America
    -4 Australasia
    -2 Antartica
    -1 Africa
    44 United Kingdom -5
    43 Austria -5
    357 Cyprus -5
    49 Germany -5
    220 Gambia -1
    44161 Greater Manchester 44
    44151 Merseyside 44
    440207 London (inner) 44
    441407 Anglesey 44
    441611 Manchester Central 44161
    441612 Salford 44161

    ...although bear in mind the above numbering is just an example not 'the' way to model such data (Ive actually modelled the numbering on the ISO telephone dialling codes +44 161 is the international code for Manchester. you may be better off using an alpha key for the ID/ParentID. use the ISO country code for the country. I used - to indictae continents (you odn't need to as sleecting rows with no parent ID woudl give you the same values (assuming of course you didn't need to add say planet as another top level )
    Last edited by healdem; 06-09-12 at 05:56.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2012
    Posts
    18
    Thank you for that. What I'm asking is how I can save the value that I selected from the combo box to the table instead of access automatically saving the ID of whats in the combo box ^_^

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    save the ID.. thats as good as the value (in fact its better than saving the value)
    when you need to extract the data use a query which pulls together the data as required.

    eg

    select my,column,list from mytable
    join Countries on Countries.ID = MyTable.CountryID

    why is it better to store the code..
    well what happens if say part through the systems lif you decide you have to change something. lets say stick with the country model. up untill 1972 Sri Lanka was known as Ceylon. in you model you would have to go in and redit every occurance of Ceylon and replace with Sri Lanka. using the ID model you change the country once and the effects are then immediately applied throughout the data. there is only one data change but every time you refer to country 4464 using the joj you will get the current value for country 4464
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    May 2012
    Posts
    18
    Right. Thanks again ^_^

Posting Permissions

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