PDA

View Full Version : Solved: Combobox with two columns



michelle
05-23-2005, 11:35 PM
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.

Bob Phillips
05-24-2005, 01:55 AM
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


MsgBox ComboBox1.List(ComboBox1.ListIndex, 1)

michelle
05-24-2005, 05:10 AM
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.

Bob Phillips
05-24-2005, 05:54 AM
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.:dunno

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

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

Norie
05-24-2005, 07:12 AM
Why are you using AddItem to fill the combobox?

Should you not populate it from data from a table?

Bob Phillips
05-24-2005, 08:28 AM
Why are you using AddItem to fill the combobox?

Should you not populate it from data from a table?

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

Norie
05-24-2005, 09:56 AM
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

Bob Phillips
05-24-2005, 11:27 AM
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


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.



By the way I fully understand the AddItem method.

Did I say otherwise?


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.




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.

xCav8r
05-24-2005, 01:41 PM
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

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.


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:

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

Or this, which is the same thing

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)

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:

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:

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.

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/default.asp?url=/library/en-us/vbaac11/html/acobjComboBox_HV03079828.asp

Norie
05-24-2005, 03:13 PM
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?

Bob Phillips
05-24-2005, 03:29 PM
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?

Norie
05-24-2005, 03:45 PM
Yes

xCav8r
05-24-2005, 07:08 PM
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?

michelle
05-25-2005, 12:08 AM
Thanks XLD for your useful information!

Michelle.