Consulting

Results 1 to 14 of 14

Thread: Solved: Combobox with two columns

  1. #1

    Solved: Combobox with two columns

    Dear VBA users,

    At the moment I am busy to create a combo box with two columns (one must must be hidden because this is the ID of a table).

    I tried everything, but the results are negative
    How can I read the hidden column after selection of the combo?

    Hope you can give a solution.

    Michelle.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by michelle
    I tried everything, but the results are negative
    How can I read the hidden column after selection of the combo?
    Index into the second dimension of the array

    [VBA]
    MsgBox ComboBox1.List(ComboBox1.ListIndex, 1)

    [/VBA]

  3. #3
    Thanks XLD for the information,

    Can you pls tell me how to fill my combobox and what properties I have to set for the combobox.

    This doesn''t work!
    With ComboBox1
    .AddItem "Create Button", 1
    .AddItem "Remove Button", 2
    End With

    Nice regards,

    Michelle.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by michelle
    This doesn''t work!
    With ComboBox1
    .AddItem "Create Button", 1
    .AddItem "Remove Button", 2
    End With
    Mic helle,

    Indeed it doesn't. I thought you already knew how to fill it from your original post, that is why I didn't give it before.

    Here is a working code snippet that should show all the principles

    [VBA]Private Sub CommandButton1_Click()
    MsgBox ComboBox1.List(ComboBox1.ListIndex, 1)
    End Sub
    Private Sub UserForm_Activate()
    Dim ary, i, j

    ary = [{"Bob","M", 123;"Lynne","F",898;"Amy","F",543}]

    With ComboBox1
    .ColumnCount = 1
    For i = 1 To 3
    .AddItem ary(i, 1)
    For j = 2 To 3
    .List(.ListCount - 1, j - 1) = ary(i, j)
    Next j
    Next i
    .ListIndex = 0
    End With

    End Sub[/VBA]

  5. #5
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Why are you using AddItem to fill the combobox?

    Should you not populate it from data from a table?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Norie
    Why are you using AddItem to fill the combobox?

    Should you not populate it from data from a table?
    Quote Originally Posted by xld
    Here is a working code snippet that should show all the principles

  7. #7
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    xld

    How does it show the concept of populating a combobox from a table/query?

    By the way I fully understand the AddItem method.

    No need for a loop.
    ary = [{"Bob","M", 123;"Lynne","F",898;"Amy","F",543}]
    ComboBox1.ColumnCount = 1
    ComboBox1.List = ary
    ary = [{"Bob","M", 123;"Lynne","F",898;"Amy","F",543}]
    ComboBox1.ColumnCount = 3
    ComboBox1.Column = ary

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Norie
    xld

    How does it show the concept of populating a combobox from a table/query?
    It doesn't, because that wasn't the question. Michelle asked

    Quote Originally Posted by michelle
    Can you pls tell me how to fill my combobox and what properties I have to set for the combobox

    This doesn''t work!
    With ComboBox1
    .AddItem "Create Button", 1
    .AddItem "Remove Button", 2
    End With.
    So I did, in very general VBA terms.


    Quote Originally Posted by Norie
    By the way I fully understand the AddItem method.
    Did I say otherwise?

    Quote Originally Posted by Norie
    No need for a loop.
    ary = [{"Bob","M", 123;"Lynne","F",898;"Amy","F",543}]
    ComboBox1.ColumnCount = 1
    ComboBox1.List = ary
    No there isn't and that works too, but I chose to use the other method so that Michelle mioght get a deeper understanding of what was happening, as she was not quite up to speed on it. That was my choice, and I made it.

    Quote Originally Posted by Norie
    ary = [{"Bob","M", 123;"Lynne","F",898;"Amy","F",543}]
    ComboBox1.ColumnCount = 3
    ComboBox1.Column = ary
    She only wants one visible column, so don't set ColumnCount to anything other than 1.

  9. #9
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    Quote Originally Posted by michelle

    Can you pls tell me how to fill my combobox and what properties I have to set for the combobox.

    This doesn''t work!
    [VBA] With ComboBox1
    .AddItem "Create Button", 1
    .AddItem "Remove Button", 2
    End With[/VBA]
    Provided that ComboBox1 is a valid object and your .RowSourceType = "Value List", this method of adding items to a ComboBox or ListBox will work with a small adjustment.

    Quote Originally Posted by Microsoft
    Syntax:

    Variant = object.AddItem [ item [, varIndex]]

    Part Description

    object Required. A valid object.

    Item Optional. Specifies the item or row to add. The number of the first item or row is 0; the number of the second item or row is 1, and so on.

    varIndex Optional. Integer specifying the position within the object where the new item or row is placed.
    Your problem is you're trying to add an item to the second row in the column before anything has been placed into the first row. Just change to this:

    [VBA] With ComboBox1
    .AddItem "Create Button", 0
    .AddItem "Remove Button", 1
    End With[/VBA]

    Or this, which is the same thing

    [VBA] With ComboBox1
    .AddItem "Create Button"
    .AddItem "Remove Button"
    End With[/VBA]

    Or this, which is a variation that changes the order of the items (puts both into the top position, meaning that "Create Button" goes to the top first, but then it's moved down to the second position when "Remove Button" is put at the top)

    [VBA] With ComboBox1
    .AddItem "Create Button", 0
    .AddItem "Remove Button", 0
    End With[/VBA]

    To get the list index value the user chooses, add this to your with statement:

    [VBA] With ComboBox1
    .AddItem "Create Button"
    .AddItem "Remove Button"
    .BoundColumn = 0
    End With[/VBA]

    If a user chooses "Create Button", then you can get the list index value (which is 0 in this case) with this:

    [VBA]ComboBox1.Value[/VBA]

    These things said, there are much easier ways to populate a ComboBox in Access. As Norie already mentioned, the most common way is to load data from a table, and after reading your first post, it sounds like that's what you want to do.

    [VBA] With ComboBox1
    .RowSourceType = "Table/Query"
    .RowSource = "TableOrQueryName"
    .ColumnCount = 2
    .ColumnWidths = "0;"
    End With
    [/VBA]

    Make sure your table or query has the ID as its first field (in the first column), and have the text you want to load to the combobox be in the adjacent field/column. The setting .ColumnWidths="0;" means that the first column will be 0 inches wide, which will hide it.

    I hope this helps!

    P.S. For reference: http://msdn.microsoft.com/library/de...HV03079828.asp

  10. #10
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Quote Originally Posted by xld
    She only wants one visible column, so don't set ColumnCount to anything other than 1.
    xld

    If you set ColumnCount to 1 it not only means that only one column is visible but it means that none of the data in other columns are accessible via VBA.

    If you want colums to be visible and hide the other columns, but still make the data in the hidden columns availble to VBA you need to use the ColumnWidths property?

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Norie
    If you set ColumnCount to 1 it not only means that only one column is visible but it means that none of the data in other columns are accessible via VBA.
    Have you actually tried that?

  12. #12
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Yes

  13. #13
    VBAX Expert xCav8r's Avatar
    Joined
    May 2005
    Location
    Minneapolis, MN, USA
    Posts
    912
    Location
    xld, what are you saying can still be done when the column count is set to one? Are you saying that you can bind another column?

  14. #14
    Thanks XLD for your useful information!

    Michelle.

Posting Permissions

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