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!