Check box controls combo box row source

[Ryan W]

I have 4 check boxes on a form along with a combo box. What I want is to
happen is if only one check box is checked a certain row source appears. But
if there is two or more check boxes checked another row source appears in the
combo box. Not sure what the code would look like.

For example: I have - Chk1, Chk2, Chk3, Chk4, Cbo1.

If only one of the chk boxes are checked I want the row source for cbo1 to be
"Hello"
elseif there are two or more chk boxes checked I want the row source to be
"Goodbye"

this is what I have:
if chk1 and chk2 = true or _
chk1 and chk2 and chk3 = true or _
chk1 and chk2 and chk3 and chk4 = true then
cbo1.rowsoure = "Hello"
else
cbo1rowsource = "Goodbye"
end if

Thanks for your help,

 

[Klatuu]

intCheckCount = Abs(Me.Chk1 + Me.Chk2 + Me.Chk3 + Me.Chk4)

If intCheckCount = 1 Then
Me.Cbo1.RowSource = "Hello"
ElseIf intCheckCount > 1 Then
Me.Cbo1.RowSource = "Goodbye"
Else
'What happens when none are checked
End If

 

[Ryan W]

how could I implement this code, not sure how to proceed. Thank you for your
help, Ryan

 

[Klatuu]

For starters, it should be in the Current event of the form, so it will cause
the combo box to have the correct row source for existing records.

Then, it gets tricky. That is because you have 4 controls that have to be
evaluated. Each time you check or uncheck a box, the value returned will
change and thereby change the rowsource of the combo.

If that is what you want, then I would use the posted code in a private
function at the top of your form module and call it from the After Update
event of all 4 combos and from the form's Current event.

Be aware, you may have to requery the combo after you have determined which
row source to use. It should not be necessary, but after the code executes
and the row source does not change, you could do the requery as the last line
of the function.

 

[Ryan W]

Klatuu,
I apologize, I am a novice.

what would the private function look like? how would that be worded? then
should I do this:

if intcheckcount =1 then
me.cbo1.rowsource= "hello"
me.cbo1.requery

????

Not sure how to proceed.

Thank you again for your assistance. Ryan

 

[Ofer Cohen]

I hope I answered your question using Klatuu idea in this post

http://www.microsoft.com/office/com...ding&mid=1d929b52-2757-4528-9fb7-59d28d4cd517

 

[Ryan W]

Thank you both for your assistance. It is working exactly the way I want it
to be. I so much appreciate your help. Ryan