Run specific query based on ComboBox Value

[netrusher]

Below is the code that I have on a comboBox AfterUpdate
Event. It runs a query and uses the value of Distinct
Behavior for the criteria. The Distinct Behaviors dropdown
is based on what is slected from a Dropdown from a Distinct
Categories ComboBox. What I would like is to run a specific
Query based on what is selected from the Distinct Behaviors
ComboBox. Any advice?

Private Sub DistinctCategories_AfterUpdate()
   On Error Resume Next
   DistinctBehaviors.RowSource = "SELECT DISTINCT ObserversSecondTbl.Behaviors " & _
            "FROM ObserversSecondTbl " & _
            "WHERE ObserversSecondTbl.Categories = '" & DistinctCategories.Value & "' " & _
            "ORDER BY ObserversSecondTbl.Behaviors;"

 

[PHV]

And the problem is ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

PHV,

The problem is it runs a on query based on the value of the
Behaviors dropdown. I want it to run a specific query based
on the value of the Behaviors dropdown. I do not just want
the criteria of one query to change.

 

[PHV]

Sorry, I still don't understand the issue.
Which query for what ?

 

[GKChesterton]

(1) -- Take out 'On Error Resume Next' -- deadly for trouble shooting.

(2) -- DistinctBehaviors.RowSource is not right, I believe. You want to use the value. You can use it in a recordset somehow, but not as presented.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[netrusher]

Currenly when I select the drowpdown from DistinctCategories
it populates DistinctBehaviors Dropdown. When I select the
Value in the DistinctBehaviors dropdown it runs a query
using the DistinctBehaviors Value as the criteria for a
Query. I would like to run a different query for each value
in the DistinctBehaviors Dropdown. Hope this explains myself
a little better. Sorry for the confusion and thanks for the
help!

Private Sub DistinctBehaviors_AfterUpdate()

DoCmd.OpenQuery "CategoriesBehaviorsMainQry"
Me.DistinctCategories = Clear
Me.DistinctBehaviors = Clear

End Sub

Private Sub DistinctCategories_AfterUpdate()

   On Error Resume Next
   DistinctBehaviors.RowSource = "SELECT DISTINCT ObserversSecondTbl.Behaviors " & _
            "FROM ObserversSecondTbl " & _
            "WHERE ObserversSecondTbl.Categories = '" & DistinctCategories.Value & "' " & _
            "ORDER BY ObserversSecondTbl.Behaviors;"
            
'   Me.DistinctCategories = Blank
 '  Me.DistinctBehaviors = Blank
            
'   On Error Resume Next
'   SystemGroup.RowSource = "SELECT DISTINCT TroubleShootingGuideTBL.SystemGroup " & _
'            "FROM TroubleShootingGuideTBL " & _
'            "WHERE TroubleShootingGuideTBL.FaultCategory = '" & FaultCategory.Value & "' " & _
'            "ORDER BY TroubleShootingGuideTBL.SystemGroup;"

 

[PHV]

So, you want to change some criteria in CategoriesBehaviorsMainQry ?

 

[netrusher]

Well, I would rather have a different query for each value
in the Distinct Behaviors Dropdown to run. The one query
already changes the criteria based on this dropdown but
I need to select different fields for each value of the
dropdown and not just the criteria.

 

[netrusher]

I went this way and it works. Thanks everyone

Private Sub DistinctBehaviors_AfterUpdate()

If Me.DistinctBehaviors = "Eye Protection" Then
DoCmd.OpenQuery "DistinctBehaviorsPPEEyeProQry"
End If