Combo Box list based on choice selections

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that has many different option groups. I would like to populate
a combo box with all the fields where option two was selected. I thought
about a query, but I need to do it only for the given instance of the form.
Tyra Horner
 
Probably the better approach would be to build a "value list" string in VBA
code, based on the form's selections, and then assign that value list to the
combo box.
 
Not knowing your form's setup (and guessing at what you want to do from the
form's controls), let's use some generic code to show how it would be done:

Dim strValueList As String
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Type = acOptionGroup Then ' test if control is an option
group
' concatenate the desired values into a string, delimited by
semicolon
strValueList = strValueList & ctl.Value & ";"
End If
Next ctl
' strip off the last semicolon delimiter
If strValueList <> "" Then strValueList = Left(strValueList,
Len(strValueList) - 1)
Me.ComboBoxName.RowSourceType = "Value List"
Me.ComboBoxName.RowSource = strValueList

--

Ken Snell
<MS ACCESS MVP>
 
If you give me your e-mail address I will send you a copy of the database. My
address is (e-mail address removed)
 
Let's work through this without having to exchange the database yet...
describing your setup is a good way to become more familiar with your
database and its design. If we get to the point where I feel it's best if I
see the database, then we'll cross that bridge then. < g >
 
OK I can do that.
I was looking at your code and playing around with it some.
I created a module with the following code.

Dim strValueList As String
Dim ctl As Control
For Each ctl In frmDatasheet.Controls
If ctl.Type = acOptionGroup Then ' test if control is an option
Group
' concatenate the desired values into a string, delimited by
semicolon
strValueList = strValueList & ctl.Value & ";"
End If
Next ctl
' strip off the last semicolon delimiter
If strValueList <> "" Then strValueList = Left(strValueList,
Len(strValueList) - 1)
frmDatasheet.Combo229.RowSourceType = "Value List"
frmDatasheet.Combo229.RowSource = strValueList

It however still does not work. I am not all that familiar with VBA, so that
could easily be the problem.
I greatly appreciate your help.
Tyra Horner
 
Define "doesn't work"... you get wrong results in the list? you get no list?

You haven't told us yet just what you want to concatenate in the list. The
code example I provided used the values of the option groups as the items to
put in the list. This likely is not what you want to use, but you'll need to
describe in more details how you use the option groups and what they tell
you on your form.

My code example was to give you a framework from which you might be able to
make changes to get the desired values into the list.


--

Ken Snell
<MS ACCESS MVP>
 
By don't work, I mean that I do not get a list. Each option group only
includes two possibilities. The values for each choice is a 1 or a 2. I would
like to populate the list with the field names of all the fields where 2 was
the option selected. How do I need to set up the combobox on the form, as
perhaps I am not linking it correctly?
Tyra Horner
 
Can you tell me how the "field" and the "option group" are related? In other
words, if the code finds that an option group control has a value of 2,
where does the code find the "field" so that it can get the value to put in
the list?

Perhaps, if you could show us some sample data -- what is on the form, and
what you want the combo box to display from those data.
 
The field and option group are related in several ways. First the option
selection is stored in its related field. Second the option group label is
the same as the field name. So if the list is populated with the option group
label for each option group where option two is selected, that would work.
On a related note, how do I link the module to the form. It seems to have
some difficulty connecting.
The form looks something like this.

1.1 Proper Labeling 1 2
1.2 Current 1 2
1.3 Proper Dilution 1 2

Instead of ones and twos there are checkboxes.
Tyra Horner
 
Linking the module to the form? You should have this code in the form's
module, not in a separate module. If you want to put this code in a public
subroutine, you'll need to pass a variable object for the form to it so that
it can be used. For now, what I'm providing is an example of how the code
would be in the form's module.

OK - so what you want is to get the value of the field whose name is in the
label with the option group's value. A bit tricky, but this code should get
us started. What this code does is "crawl" through all the form's controls;
when it finds an option button, it tests the Option Value of that button
against the value of the option group to which it belongs; if they match and
the value is 2, the code reads the caption of the label that is attached to
the option button and then reads the value of the field from the form's
recordset and puts it into the value list string. When done, the value list
is assigned to the combo box.

Dim strValueList As String, strValue As String
Dim ctl As Control
strValueList = ""
For Each ctl In Me.Controls
If ctl.ControlType = acOptionButton Then
If ctl.OptionValue = ctl.Parent.Value And ctl.OptionValue = 2 Then
strValue = Me.Recordset.Fields(ctl.Controls(0).Caption).Value
strValueList = strValueList & strValue & ";"
End If
End If
Next ctl
If strValueList <> "" Then
strValueList = Left(strVL, Len(strVL) - 1)
Me.Combo229.RowSourceType = "Value List"
Me.Combo229.RowSource = strValueList
End If
 
Back
Top