Populating a Combo Box with other control values

G

Guest

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?
 
J

Jeff Boyce

Cravaus

Combo boxes are based on queries. Queries are based on tables (and other
queries).

You described basing a combo box/query on a form's text boxes. I don't see
how that can happen.

Perhaps if you provided an example of actual data that would show up...?
 
G

Guest

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?
Click to expand...

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
 
G

Guest

Jeff:

Thank you for your response. This is the task I am trying to accomplish: A
patient record contains eight fields for 4 diagnoses codes and 4 diagnosis
definitions: DxCode1, DxDef1, DxCode2, DxDef2, DxCode3, DxDef3, DxCode4,
DxDef4. These are all entered into the record on a table through controls on
a form. In the same record there are six fields for service codes. Each
service field needs to be paired with a diagnosis field. I want to be able
to select from a combo box on the form one of the diagnosis codes already
entered. The resulting data would show up in the combo box in this fashion
Col1 Col2
DxCode1, DxDef1
DxCode2, DxDef2
DxCode3, DxDef3
DxCode4, DxDef4

Column one would be the bound column.

I am trying to transfer over my custom Outlook database to Access and
occasionally I am running into little snags like this. In my Outlook form I
populated the combo box using VBS and filled an array variable which then
populated my combo box cells. I am not sure how to do this in VBA for Access
or if Access even supports arrays. I have very little programming knowledge.
What I accomplish has been by trial and error in most cases. This is one of
those cases.

For illustration sake, the following is my VBS code in Outlook, I am
wondering how I can do the same in Access 2003:

Sub Item_Open()
Dim DXArray (1,3)

Set DXCombo1 = Item.GetInspector.ModifiedFormPages("Billing").DXCombo1
Set DXCombo2 = Item.GetInspector.ModifiedFormPages("Billing").DXCombo2
Set DXCombo3 = Item.GetInspector.ModifiedFormPages("Billing").DXCombo3
Set DXCombo4 = Item.GetInspector.ModifiedFormPages("Billing").DXCombo4
Set DXCombo5 = Item.GetInspector.ModifiedFormPages("Billing").DXCombo5
Set DXCombo6 = Item.GetInspector.ModifiedFormPages("Billing").DXCombo6

DXCombo1.ColumnCount = 2 'The list box contains 2 data columns
DXCombo2.ColumnCount = 2
DXCombo3.ColumnCount = 2
DXCombo4.ColumnCount = 2
DXCombo5.ColumnCount = 2
DXCombo6.ColumnCount = 2

'Load columns 1 and 2 of DXArray

DXArray(0, 0) = Item.GetInspector.ModifiedFormPages("Diagnosis").DxCode1
DXArray(1, 0) = Item.GetInspector.ModifiedFormPages("Diagnosis").DxDef1
DXArray(0, 1) = Item.GetInspector.ModifiedFormPages("Diagnosis").DxCode2
DXArray(1, 1) = Item.GetInspector.ModifiedFormPages("Diagnosis").DxDef2
DXArray(0, 2) = Item.GetInspector.ModifiedFormPages("Diagnosis").DxCode3
DXArray(1, 2) = Item.GetInspector.ModifiedFormPages("Diagnosis").DxDef3
DXArray(0, 3) = Item.GetInspector.ModifiedFormPages("Diagnosis").DxCode4
DXArray(1, 3) = Item.GetInspector.ModifiedFormPages("Diagnosis").DxDef4

'Load data into DXCombo1-6
DXCombo1.Column() = DXArray
DXCombo2.Column() = DXArray
DXCombo3.Column() = DXArray
DXCombo4.Column() = DXArray
DXCombo5.Column() = DXArray
DXCombo6.Column() = DXArray

End Sub
 
G

Guest

SteveS
Great. That was what I needed. I knew it would be simple. This is how I
used your info minus the after update routines:

Private Sub Fill_Combo()

Dim ValueList As String

ValueList = ""
ValueList = "'" & [211 DX Code] & "'"
ValueList = ValueList & ", " & "'" & [211 DX Def] & "'"
ValueList = ValueList & ", " & "'" & [212 DX Code] & "'"
ValueList = ValueList & ", " & "'" & [212 DX Def] & "'"
ValueList = ValueList & ", " & "'" & [213 DX Code] & "'"
ValueList = ValueList & ", " & "'" & [213 DX Def] & "'"
ValueList = ValueList & ", " & "'" & [214 DX Code] & "'"
ValueList = ValueList & ", " & "'" & [214 DX Def] & "'"

With Me.[24E1 DX]
.RowSource = ValueList
With Me.[24E2 DX]
.RowSource = ValueList
With Me.[24E3 DX]
.RowSource = ValueList
With Me.[24E4 DX]
.RowSource = ValueList
With Me.[24E5 DX]
.RowSource = ValueList
With Me.[24E6 DX]
.RowSource = ValueList
End With
End With
End With
End With
End With
End With
End Sub

This works. A little trial and error coding, as is my way, on top of some
elses expert know how. I have also used the "On Enter" event rather than
"click" to load the combo boxes and all is well.

Thank you again
Cravaus
 
J

Jeff Boyce

It appears you have repeating fields in your table definition. This may be
the only way to make something like what you have work in a spreadsheet. If
you are using Access, you could get much more of its functionality and
power, at a much lower "cost", if you used a well-normalized data structure.

Given the way your data appears to be structured now, I understand why you
were trying to do what you asked.

Consider checking into normalization and relational database design...
 
G

Guest

Cravaus said:
SteveS
Great. That was what I needed. I knew it would be simple. This is how I
used your info minus the after update routines:

Private Sub Fill_Combo()

Dim ValueList As String

ValueList = ""
ValueList = "'" & [211 DX Code] & "'"
ValueList = ValueList & ", " & "'" & [211 DX Def] & "'"
ValueList = ValueList & ", " & "'" & [212 DX Code] & "'"
ValueList = ValueList & ", " & "'" & [212 DX Def] & "'"
ValueList = ValueList & ", " & "'" & [213 DX Code] & "'"
ValueList = ValueList & ", " & "'" & [213 DX Def] & "'"
ValueList = ValueList & ", " & "'" & [214 DX Code] & "'"
ValueList = ValueList & ", " & "'" & [214 DX Def] & "'"

With Me.[24E1 DX]
.RowSource = ValueList
With Me.[24E2 DX]
.RowSource = ValueList
With Me.[24E3 DX]
.RowSource = ValueList
With Me.[24E4 DX]
.RowSource = ValueList
With Me.[24E5 DX]
.RowSource = ValueList
With Me.[24E6 DX]
.RowSource = ValueList
End With
End With
End With
End With
End With
End With
End Sub

This works. A little trial and error coding, as is my way, on top of some
elses expert know how. I have also used the "On Enter" event rather than
"click" to load the combo boxes and all is well.

Thank you again
Cravaus
Click to expand...

Excelent. I was just playing around with the afterupdate code for the text
boxes.

BTW, since you are only changing one property, you might try using

Me.[24E1 DX].RowSource = ValueList
Me.[24E2 DX].RowSource = ValueList
Me.[24E3 DX].RowSource = ValueList
Me.[24E4 DX].RowSource = ValueList
Me.[24E5 DX].RowSource = ValueList
Me.[24E6 DX].RowSource = ValueList


It might be a little faster (a nanosecond or two, but ... <g> ...it adds
up...) and uses less resources than if they are nested.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to color boxes in continuous subform? 3
Calculation 2
use combo box selection as subform query criteria 2
Problem: Combo boxes self-populating 11
Populating text boxes bepending on a Combo box 3
Self-Populating Combo Box 1
Combo Box returning results 1
Conditional Combo Box 3

Top