Cravaus said:
I need to populate a two column list in a combo box with values contained in
8 text boxes on my form. The result would be a drop down list with values
such as this:
Box1,Box2;
Box3,Box4;
Box5,Box6;
Box7,Box8;
I have no idea how to get this done. I have tried to list the text boxes in
a values list and that will not work. I can not make it happen with a query.
I have messed around with VBA and I am just not getting it. I would think
that this would be a simple task. Any help out there for me?
A combo box Row Source can be a Table/Query, A Field List or a Value List.
For your requirements, Table/Query won't work. Neither will Field List. So
you are left with Value List. (Look up Combo box in Help)
To enter/change the row source you must do it manually or use VBA.
When a combo box is in the dropped down state, all columns with a non-zero
width are displayed. When not in the dropped down state, only the *first*
non-zero column width is displayed. If you want to display two columns
(values) in the non-dropped down state, you create a calculated field.
A combo box is able to store only ONE value, the bound column.
It is very unclear what you are trying to do. What are the columns you want
to display in the copmbo box? Names, types, values?
But, to get you started, set the combo box Row Source Type to "Value List".
To fill the Row Source, you need to use a little VBA.
If you have 8 text boxes named Box1 - Box8 and a combo box named Combo0,
enter/ copy & paste the following code.
Note that there is no error handling; what do you want to happen if a box
has no entry? Box1 is empty (null) and Box2 has an entry?
With your form is design view, press Control-G to open the code editor.
'***** begin code ***************
' Creates & loads a value list into the combo box
Private Sub Fill_Combo()
Dim ValueList As String
ValueList = ""
ValueList = "'" & box1 & ", " & box2 & "'"
ValueList = ValueList & ", " & "'" & box3 & ", " & box4 & "'"
ValueList = ValueList & ", " & "'" & box5 & ", " & box6 & "'"
ValueList = ValueList & ", " & "'" & box7 & ", " & box8 & "'"
With Me.Combo0
.RowSource = ValueList
End With
End Sub
' updates the combo box after a text box is changed
Private Sub box1_AfterUpdate()
Fill_Combo
End Sub
Private Sub box2_AfterUpdate()
Fill_Combo
End Sub
Private Sub box3_AfterUpdate()
Fill_Combo
End Sub
Private Sub box4_AfterUpdate()
Fill_Combo
End Sub
Private Sub box5_AfterUpdate()
Fill_Combo
End Sub
Private Sub box6_AfterUpdate()
Fill_Combo
End Sub
Private Sub box7_AfterUpdate()
Fill_Combo
End Sub
Private Sub box8_AfterUpdate()
Fill_Combo
End Sub
Private Sub Command18_Click()
Fill_Combo
End Sub
'***** end code *******
HTH