Combobox with 2 columns issues

[cjmitton]

I have a form in Word that has 2 comboboxes, I'm using a SQL Database to fill the combo boxes.
the first combobox fills correctly using the following code when initializing the form:



rs1.Open "SELECT Distinct WorkTypeID, WorkTypeTitle FROM WorkType", cn
rs1.MoveFirst
With Me.CmboWorkType
.Clear
Do
.AddItem rs1![WorkTypeID]
.List(i, 0) = rs1![WorkTypeID]
.List(i, 1) = rs1![WorkTypeTitle]
i = i + 1
rs1.MoveNext
Loop Until rs1.EOF
.ListIndex = 0
End With


My first issue is selecting the first item in the list so its visible on the combobox. I normally (with single column comboboxes) use .listindex = 0 and the first item is shown. With this combobox I only see the first column but if I click on the combobox the items are shown correctly (i.e. column 1 and column 2). When I select a new item it changes to display just the first column again., its the correct information!

My second issue works along with the first...

I really only want to display the second column, the first being the ID field for the work type needs to be hidden away. On the properties for the combobox under the data section:
BoundColumn is 1
ColumnCount is 2
ColumnWidths is (Currently: 50 pt, 100 pt) but changed to 0 pt, 100 pt

This did hide the first column but (with issue 1) nothing was visible on the first combobox. Then second combobox would not work. This was due to the ID field selected in the first combobox being used in the second combobox SQL statement.

The Code I've used is below:


Dim WrkType As String
WrkType = CmboWorkType.Column(0)
Dim rs2 As New ADODB.Recordset
cn.Open
rs2.Open "SELECT Distinct AddTypeTitle FROM AddType WHERE AddWorkType = '" & WrkType & "'", cn
rs2.MoveFirst
With Me.CmboType
.Clear
Do
.AddItem rs2![AddTypeTitle]
rs2.MoveNext
Loop Until rs2.EOF
.ListIndex = 0
End With


The code above will eventually get two columns like the first combobox, I need to use the 'ID' from each for code when populating a listbox on the form but want to get the comboboxes working first.

This code does work when both columns on the first combobox are visible.

So! How can I just display the second columns but use the first column for building the second combobox and (in the future) the list box that's on the form?

Hope I've explained that well!

[gmaxey]

Private Sub UserForm_Initialize()
With Me.ListBox1
.Clear

.AddItem
.List(.ListCount - 1, 0) = "123"
.List(.ListCount - 1, 1) = "Ball"
.AddItem
.List(.ListCount - 1, 0) = "124"
.List(.ListCount - 1, 1) = "Bat"

.ColumnCount = 2
.ColumnWidths = "0;20"
.ListIndex = 0
End With
End Sub
Private Sub ListBox1_Click()
MsgBox ListBox1.Column(0) & " is the id, " & ListBox1.Column(1) & " is the value."
End Sub

[cjmitton]

Thanks Greg, That worked...

I added the .columncount and .columnwidths in and it worked. Bit confused as I thought that adding those setting in the properties for the combobox would do that same thing? for my own curiosity is this just a quirk of VBA or is there a reason?

[gmaxey]

Here with the properties that are stetted out put in the listbox properties dialog it works the same. Don't know why it wouldn't for you:


Private Sub UserForm_Initialize()
With Me.ListBox1
.Clear
.AddItem
.List(.ListCount - 1, 0) = "123"
.List(.ListCount - 1, 1) = "Ball"
.AddItem
.List(.ListCount - 1, 0) = "124"
.List(.ListCount - 1, 1) = "Bat"
'.ColumnCount = 2
'.ColumnWidths = "0;20"
.ListIndex = 0
End With
End Sub

[cjmitton]

Strange but as I'm loading from a recordset not an array or manually added it might just be a quirk.

Hey it works and that's all that matters :) Thanks again for the assistance.