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.

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

Code:

---


MsgBox ComboBox1.List(ComboBox1.ListIndex, 1)

---

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!

Code:

---

With ComboBox1
    .AddItem "Create Button", 1
    .AddItem "Remove Button", 2
End With

---

Nice regards,

Michelle.

Quote:

---

Originally Posted by michelle

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

---

Michelle,

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.:dunno

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

Code:

---

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

---

Why are you using AddItem to fill the combobox?

Should you not populate it from data from a table?

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

---

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.

Code:

---

ary = [{"Bob","M", 123;"Lynne","F",898;"Amy","F",543}]
ComboBox1.ColumnCount = 1
ComboBox1.List = ary

---

Code:

---

ary = [{"Bob","M", 123;"Lynne","F",898;"Amy","F",543}]
ComboBox1.ColumnCount = 3
ComboBox1.Column = ary

---

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!

Code:

---

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.

Code:

---

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

Code:

---

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.

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!

Code:

---

With ComboBox1
    .AddItem "Create Button", 1
    .AddItem "Remove Button", 2
End With

---

---

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:

Code:

---

With ComboBox1
    .AddItem "Create Button", 0
    .AddItem "Remove Button", 1
End With

---

Or this, which is the same thing

Code:

---

With ComboBox1
    .AddItem "Create Button"
    .AddItem "Remove Button"
End With

---

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)

Code:

---

With ComboBox1
    .AddItem "Create Button", 0
    .AddItem "Remove Button", 0
End With

---

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

Code:

---

With ComboBox1
    .AddItem "Create Button"
    .AddItem "Remove Button"
    .BoundColumn = 0
End With

---

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

Code:

---

ComboBox1.Value

---

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.

Code:

---

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

---

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

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?

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?

Yes

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?

Thanks XLD for your useful information!

Michelle.