Update the rowsource property of a second control

You may need a

Me.cboConfirmation_Phase_1.requery

In there after you set the RowSource.

Try running that RowSource query on its own as a stand-alone query. Does it return any rows?

shouldn't need the requery.  The act of changing the rowsource forces a requiry.  Try:

strSQL = "SELECT t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1 " _
            & "FROM t_0_MAIN_Downgrade_Tracking, t_2_JUNCTION_WORKFLOW " _
            & "WHERE Status_Phase_1 = " & me.cboStatus_Phase_1 & " " _
            & "GROUP BY t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1;"

You need to concatenate the SQL with the value from the first combo box.

Try this if me.cboStatus_Phase_1 is numeric:

Me.cboConfirmation_Phase_1.RowSource = "SELECT t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1 " & _
                             "FROM t_0_MAIN_Downgrade_Tracking, t_2_JUNCTION_WORKFLOW " & _
                             "WHERE Status_Phase_1 = " & me.cboStatus_Phase_1 & " & _
                             "GROUP BY t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1;"

         Me.cboConfirmation_Phase_1.Requery

Select allOpen in new window

If  me.cboStatus_Phase_1 is text then

 

Me.cboConfirmation_Phase_1.RowSource = "SELECT t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1 " & _
                             "FROM t_0_MAIN_Downgrade_Tracking, t_2_JUNCTION_WORKFLOW " & _
                             "WHERE Status_Phase_1 = """ & me.cboStatus_Phase_1 & """ & _
                             "GROUP BY t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1;"

Me.cboConfirmation_Phase_1.Requery

Select allOpen in new window

You had the reference to the first combo inside the quotes, so it was never actually seeing the value of that control.

Also, this query uses a Cartesian Join, which can involve a lot of results.  Is there a field in common between your tables [t_0_MAIN_Downgrade_Tracking] and [t_2_Junction_Workflow] that you could use to join the two tables.

BTW, I usually build the SQL then apply it to the rowsource so I can use:

debug.print strSQL

to test the value that is actually being assigned to the rowsource.

Hi Experts,

I get a compile error if I try TheHighTechCoach solution.

If I try fyed's solution, I do get dropdown options back, however, they are still unfiltered (i.e. they are from the ComboBox's original control row source:  SELECT t_1_COMBO_Confirmation.Confirmation_ID, t_1_COMBO_Confirmation.Confirmation FROM t_1_COMBO_Confirmation ORDER BY t_1_COMBO_Confirmation.Confirmation;)

This is my attempt at fyed's code:

Private Sub cboStatus_Phase_1_AfterUpdate()
Dim strsql As String
strsql = "SELECT t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1 " _
            & "FROM t_0_MAIN_Downgrade_Tracking, t_2_JUNCTION_WORKFLOW " _
            & "WHERE Status_Phase_1 = " & Me.cboStatus_Phase_1 & " AND Action_Type_Phase_1_ID = " & Me.cboAction_Type_Phase_1_ID & " " _
            & "GROUP BY t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1;"
   
Debug.Print strsql
End Sub

so, whey you tried it using my code, what got printed out in the debug window?  

That looks like it should be correct (assuming that [Status_Phase_1] is numeric and the bound column in me.cboStatus_Phase_1 is also numeric, and that the same applies for [Action_type_Phase_1_ID]

But you forgot to set the rowsource.

me.cbo_ThirdField.Rowsource = strSQL

The other point is that if you are doing this for the cbo_Status_Phase_1_AfterUPdate event, does the other combo even have a value at that point?  

Can you take a screen shot of your form in design view, so that we can see the controls and the order they are in?

Hi fyed,

Yes, all numeric and the other combo has a value.  I have stripped out confidential information and attached my database....starting to get confused by the SQL bit...it's looking like I bit off more than I can chew!  Maybe the way the rest of the database works interferes with filtering?  Is the after update event the right one to choose?

(The combo boxes I am working on are on the first tab of 1_Main_Form)

Thanks!
Expert-Exchange.accdb

OK,

Now that I've had a chance to take a look at your data, can you explain to me what your t_2_JUNCTION_Workflow table is supposed to do?  It has a compound primary key containing six fields, I assume the point of that is to ensure uniqueness of each record in that table.  But what is it supposed to do?


I think what you probably want as the row source of cboConfirmation_Phase_1 is:

Private Sub cboStatus_Phase_1_AfterUpdate()

    Dim strsql As String
    strsql = "SELECT t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1" _
              & " FROM t_2_JUNCTION_WORKFLOW" _
              & " WHERE Status_Phase_1 = " & Me.cboStatus_Phase_1 _
              & " AND Action_Type_Phase_1_ID = " & Me.cboAction_Type_Phase_1_ID
    Debug.Print strsql
    Me.cboConfirmation_Phase_1.RowSource = strsql
   
End Sub

First,  verify that the RowSourceType property is set to Table/Query

---

In this situation, I would expand on vadimrapp1's suggestion --- keep the RowSource as a 'fixed' SQL statement that references the form controls ...

SELECT t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1
FROM t_2_JUNCTION_WORKFLOW
WHERE Status_Phase_1 = CLng(Nz([Forms]![nameOfYourFormObject]![cboStatus_Phase_1],-1))
AND Action_Type_Phase_1_ID = CLng(Nz([Forms]![nameOfYourFormObject]![Me.cboAction_Type_Phase_1_ID],-1))

Select allOpen in new window

Then, since the RowSource is dependent upon TWO controls, you need to trigger a requery of the RowSource after an update of either of the two dependancies.

Private Sub cboStatus_Phase_1_AfterUpdate()
    Me.cboConfirmation_Phase_1.Requery
End Sub

Private Sub Action_Type_Phase_1_ID_AfterUpdate()
    Me.cboConfirmation_Phase_1.Requery
End Sub

Select allOpen in new window

---

If you don't like the 'Fixed' SQL concept, then I suggest that you create a Private Function in the code behind your Form object that looks what Fyed modeled ...

Private Function pfSetConfirmationRowSource() As Byte

    Dim strsql As String

    strsql = "SELECT t_2_JUNCTION_WORKFLOW.Confirmation_Phase_1" _
              & " FROM t_2_JUNCTION_WORKFLOW" _
              & " WHERE Status_Phase_1 = " & Nz(Me.cboStatus_Phase_1,-1) _
              & " AND Action_Type_Phase_1_ID = " & Nz(Me.cboAction_Type_Phase_1_ID,-1)
 
   Debug.Print strsql

    Me.cboConfirmation_Phase_1.RowSource = strsql
    
End Sub 

Select allOpen in new window

Then set the AfterUpdate PROPERTY for cboStatus_Phase_1 and Me.cboAction_Type_Phase_1_ID to ...

=pfSetConfirmationRowSource()

---

{Note: that the SQL statements I proposed handle the possibility of Null values in those controls.  I coerced the Null values to -1 with the assumption that a -1 would NOT exist is the domain of the values that populate the respective fields.  I did that with the assumption you would NOT want records returned if either dependent control was Null/Blank.  If that is not the preferred behavior, then let us know and we can guide you as to how to change that behavior}

As pathetic as this sounds, I'm super excited about this solution, it solves alot of my problems!

Shoot, I didn't see datAdrenaline's expansion until after I accepted the solution!

No problem ... I will just continue to sit here trying desperately to get 200 points to maintain my status for this month ... woes me ... alms for the poor ... <moan and groan> ... LOL!!!

---

<note: sarcasm, humor, etc. heavily applied above!>

----
Now the sincere part:

Good luck on your project!  Glad you got the help you needed!

Thanks so much datAdrenaline, you have just saved me from having to struggle with the next part of my problem or having to write a second question.  I learn so much from the Experts and appreciate all responses.....I would go back and give you 1000 points if I could!

@datAdrenaline,

https://www.experts-exchange.com/questions/28225205/Rowsource-lookup-by-other-than-bound-column.html

btw, you may want to update the link in your profile/about me

Thanks! ... Updated the you referenced. :)