Populating ComboBox from Yes/No checkbox

A

aldeb

Registered User.
Local time
Today, 11:27
Joined
Dec 23, 2004
Messages
318
Below is code I am using on a form. When the PPEEyeProUnsafe
checkbox is true, the code automatically has the Cagegories
ComboBox populate with one of the dropdowns "Personal
Protective Equipment". That works fine. When the
Personal Protective Equipment is selected in that combobox
it cascades to what will appear in the Behaviors ComboxBox.
When something is selected in the Behaviors ComboxBox that
cascades to what will appear in the UnsafeDescription
ComboBox. That works too.

My problem is
When I check another CheckBox i.e. BodyPosition the
Categories Box will populate with the Body Position
value which is also one of the Categories CombBox dropdowns
but then the Behaviors ComboBox still has the values
that go with the Personal Protective Equipment part of
the Categories ComboBox. If I physically select from the
Categories ComboBox, everything works well. When I am just
populating the Categories ComboBox from the Yes/No it does
not update the Behaviors Combo.

I am sorry this is so long and I hope I have explained it
well enough for someone to understand.

Code: 
Private Sub PPEEyeProUnsafe_AfterUpdate()
    
If Me.PPEEyeProUnsafe = True Then
Me.Categories.Visible = True
Me.Behaviors.Visible = True
Me.UnsafeDescription.Visible = True
Me.Comments.Visible = True
Me.UnsafeBox.Visible = True
Me.update_rec.Visible = True
Me.Categories.Value = "Personal Protective Equipment"
Else
If Me.PPEEyeProUnsafe = False Then
Me.Categories.Visible = False
Me.Behaviors.Visible = False
Me.UnsafeDescription.Visible = False
Me.Comments.Visible = False
Me.UnsafeBox.Visible = False
Me.update_rec.Visible = False
Me.Categories.Value = ""
  
End If
End If

End Sub
Code: 
Private Sub Categories_AfterUpdate()

   On Error Resume Next
   Behaviors.RowSource = "SELECT DISTINCT ObserversCategoriesTbl.Behaviors " & _
            "FROM ObserversCategoriesTbl " & _
            "WHERE ObserversCategoriesTbl.Categories = '" & Categories.Value & "' " & _
            "ORDER BY ObserversCategoriesTbl.Behaviors;"
Me.Behaviors = Blank
Me.UnsafeDescription = Blank

End Sub

Private Sub Behaviors_AfterUpdate()

   On Error Resume Next
   UnsafeDescription.RowSource = "SELECT DISTINCT ObserversCategoriesTbl.UnsafeDescription " & _
            "FROM ObserversCategoriesTbl " & _
            "WHERE ObserversCategoriesTbl.Behaviors = '" & Behaviors.Value & "' " & _
            "ORDER BY ObserversCategoriesTbl.UnsafeDescription;"

End Sub
 
You would need to do something like Me.cboMyCombo.requery and the combo boxes will show the new record source.
 
When a Check Box is selected (regardless of which one) have only the Categories Combo Box enabled and the other two Combo Boxes disabled so that selections can not be made from them until a selection is made from the Categories Combo Box which ultimately then populates the Behaviors Combo Box.

After the Behaviors Combo Box is populated it then is enabled so that a selection can be made from that list. Upon selection of a item within the Behaviors Combo Box drop down list, the UnsafeDescription Combo Box is then populated and then enabled so that a selection can also be made there.

If another Check Box is selected then the whole process starts over again.

Changing the RowSource property of a Combo Box or List Box during Run Time should automatically cause a Requery on that control.

.
 
Uncle Gizmo,

Where would I insert that code?
 
Cyber Lynx,

Thanks for your answer.

The whole point is I do not want them to hav to select from the ComboBox Categories.
I want that box to populate automatically when one of the Checkbox's is checked.
Any other suggestions to solve my issue will be appreciated.
 
in any control that causes a change in the rowsource, then you would need to query that combo box.

So for example when you click on a check box, look in the check box properties and you will see the "after update event" put the code there.
 
Instead of

Private Sub PPEEyeProUnsafe_AfterUpdate()

If Me.PPEEyeProUnsafe = True Then
Me.Categories.Visible = True
Me.Behaviors.Visible = True
Me.UnsafeDescription.Visible = True
Me.Comments.Visible = True
Me.UnsafeBox.Visible = True
Me.update_rec.Visible = True
Me.Categories.Value = "Personal Protective Equipment"
Else
If Me.PPEEyeProUnsafe = False Then
Me.Categories.Visible = False
Me.Behaviors.Visible = False
Me.UnsafeDescription.Visible = False
Me.Comments.Visible = False
Me.UnsafeBox.Visible = False
Me.update_rec.Visible = False
Me.Categories.Value = ""

End If
End If

End Sub
Click to expand...

Why not...

Me.Categories.Visible = Me.PPEEyeProUnsafe
Me.Behaviors.Visible = Me.PPEEyeProUnsafe
Me.UnsafeDescription.Visible = Me.PPEEyeProUnsafe
Me.Comments.Visible = Me.PPEEyeProUnsafe
Me.UnsafeBox.Visible = Me.PPEEyeProUnsafe
Me.update_rec.Visible = Me.PPEEyeProUnsafe
Click to expand...

This cuts out the evaluation process.
 
Uncle Gizmo,

I have inserted the code below and I have requeried the ComboBox at every location
where I call the ComboBox but it is not working. Is the ComboBox name the same as
the Field Name I am requerying?

Code: 
   Dim Bool As Boolean
   
   Bool = (Me.PPEEyeProUnsafe = True)
   Me.Categories.Requery
   
   Me.Categories.Visible = Bool
   Me.Behaviors.Visible = Bool
   Me.UnsafeDescription.Visible = Bool
   Me.Comments.Visible = Bool
   Me.UnsafeBox.Visible = Bool
   Me.update_rec.Visible = Bool
   Me.Categories = Choose(Bool + 2, "Personal Protective Equipment", "")
   
   Me.Categories.Requery
Code: 
   Bool = (Me.PPEBodyProUnsafe = True)
   
   Me.Categories.Visible = Bool
   Me.Behaviors.Visible = Bool
   Me.UnsafeDescription.Visible = Bool
   Me.Comments.Visible = Bool
   Me.UnsafeBox.Visible = Bool
   Me.update_rec.Visible = Bool
   Me.Categories = Choose(Bool + 2, "Personal Protective Equipment", "")
   
   Me.Categories.Requery]
 
Uncle Gizmo:

Below is the code I have on two different checkboxes
AfterUpdate. When I select the first checkbox the I get
Categories Personal Protective Equipment and in the
Behaviors and UnsafeDescription combo boxes I get the
appropriate lists in the dropdowns.

When I click the second checkbox I get the Categories
Body Position but the Behaviors and UnsafeDescription
comboboxes is not updating to appropriate lists for
the Behaviors and UnsafeDescription.

I was hoping the requery of those boxes would handle this.
Maybe I have them in the wrong position.

If I manually select from the Categories combox the
Behaviors and UnsafeDescription comboxes do populate with
the approriate date. HELP!! What am I doing wrong?


Code: 
Private Sub PPEEyeProUnsafe_AfterUpdate()
   
   Dim Bool As Boolean
   
   Bool = (Me.PPEEyeProUnsafe = True)
   
   Me.Categories.Visible = Bool
   Me.Behaviors.Visible = Bool
   Me.UnsafeDescription.Visible = Bool
   Me.Comments.Visible = Bool
   Me.UnsafeBox.Visible = Bool
   Me.update_rec.Visible = Bool
   Me.Categories = Choose(Bool + 2, "Personal Protective Equipment", "")
Categories.Requery
Behaviors.Requery
UnsafeDescription.Requery
Code: 
Private Sub BodyEyesHandsTaskPathUnsafe_AfterUpdate()

   Bool = (Me.BodyEyesHandsTaskPathUnsafe = True)
   
   Me.Categories.Visible = Bool
   Me.Behaviors.Visible = Bool
   Me.UnsafeDescription.Visible = Bool
   Me.Comments.Visible = Bool
   Me.UnsafeBox.Visible = Bool
   Me.update_rec.Visible = Bool
   Me.Categories = Choose(Bool + 2, "Body Position", "")
Categories.Requery
Behaviors.Requery
UnsafeDescription.Requery
 
Last edited:
You must log in or register to reply here.

Similar threads

C
Solved Runtime error 3071
Replies
6
Views
906
chrisjames25
C
T
Filter Form From ComboBox and date field
Replies
4
Views
691
Josef P.
J
R
Solved Checkboxes with Yes / No
Replies
4
Views
937
Pat Hartman
P
austinsneeze
Bootcamp Homework Help: Option Button Isn't Populating Query
Replies
5
Views
625
MajP
MajP
D
Solved Populate Form & Associated Table with Values from another Table
Replies
5
Views
761
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom