Revese a cascade comboboxes

[epardo87]

I have been trying to figure this out for a few days now and haven't been able to solve it, I am very delayed in this project now and I feel this is almost the last step to solve.

So I have these two froms, the red arrows show the current cascade flow, working perfectly fine, and the queries highlighted at the left are the ones filtering each combobox and listbox.

I would like to know how to reverse the flow, if I select a different option in the second form, I would like it to go and be pasted in the third combobox of the first form, and also fill the corresponding options of the first two comboboxes.

any suggestion is highly appreciated

[John_G]

As long as there are no duplicates in the table column that Estacion is displaying, you can do this:

- Use Dlookup to find the Area associated with the Asignaciones value selected on Form 2
- Set combo1 on Form1 to that value
- Run the After_Update event procedure for Combo1 (to reset the list in combo2)
- Use Dlookup to find the SubArea associated with the Asignaciones value selected on Form 2
- Set combo2 on Form1 to that value
- Run the After_Update event procedure for Combo2 (to reset the list in combo3)
- Set Combo3 on Form1 to the value selected on Form2.
- Run the After_Update event procedure for Combo3 (to reset the Asignados list on Form1)


This assumes that the 3 items listed on Form2 might be from different Area - Sub-Area combinations

I think that is what you need, based on what you diagram shows.

[epardo87]

As long as there are no duplicates in the table column that Estacion is displaying, you can do this:

- Use Dlookup to find the Area associated with the Asignaciones value selected on Form 2
- Set combo1 on Form1 to that value
- Run the After_Update event procedure for Combo1 (to reset the list in combo2)
- Use Dlookup to find the SubArea associated with the Asignaciones value selected on Form 2
- Set combo2 on Form1 to that value
- Run the After_Update event procedure for Combo2 (to reset the list in combo3)
- Set Combo3 on Form1 to the value selected on Form2.
- Run the After_Update event procedure for Combo3 (to reset the Asignados list on Form1)


This assumes that the 3 items listed on Form2 might be from different Area - Sub-Area combinations

I think that is what you need, based on what you diagram shows.

Thanks for your answer John_G,

That is exactly what I need, I have actually tried to follow that series of steps but the thing is that I'm very new with access and just can't figure out exactly how to have access do that. I'm having trouble with the coding, I've tried macro building, expression builder and code as well but, just can't get with any...

I tried coding the steps and wrote this:

Private Sub btnStation_Click()
    Dim AreaLookUp As String
      AreaLookUp = DLookup("[Area]", "[Qryxreference]", "[Station]='" & [Forms]![frm30UserData]![lstStation] & "'")
'not sure if the following is correct to set the value
    [Forms]![frm20MainStatus]![cmbArea] = AreaLookUp

'how do I run the after update event for cmbArea?


    Dim SubLookUp As String
      SubLookUp = DLookup("[Subarea]", "[Qryxreference]", "[Station]='" & [Forms]![frm30UserData]![lstStation] & "'")
    [Forms]![frm20MainStatus]![cmbSub] = SubLookUp

'after update event for cmbSub

[Forms]![frm20MainStatus]![cmbStation] = [Forms]![frm30UserData]![lstStation] & "'"

'after update event for cmbStation

End Sub

I know that [Forms]![frm20MainStatus]![cmbArea], works in expression builder but don't know if it's the same in code...

I have an afterupdate code inside each combobox to refresh following comboboxes, but don't know how to run it from the button code so that I can keep with the following steps...
I will be forever thankful if you could help me correct the code, or instruct a better way to have those steps work

[epardo87]

Ok I just pasted it like that and it worked! I just had to add a final refresh so that the listbox updated the values!

this was the code that made it work:

Private Sub btnStation_Click()    Dim AreaLookUp As String
      AreaLookUp = DLookup("Area", "Qry-xreference", "StationDescription='" & Forms!frm30UserData!lstStation & "'")
    [Forms]![frm20MainStatus]![cmbArea] = AreaLookUp
    Dim SubLookUp As String
      SubLookUp = DLookup("[Subarea]", "[Qry-xreference]", "[StationDescription]='" & [Forms]![frm30UserData]![lstStation] & "'")
    [Forms]![frm20MainStatus]![cmbSub] = SubLookUp
[Forms]![frm20MainStatus]![cmbStation] = [Forms]![frm30UserData]![lstStation] & ""
[Forms]![frm20MainStatus].Refresh
End Sub

Thanks for yout help!

[John_G]

You're on the right track. First though, you have to make the event procedures in form1 accessible to Form2. Open Form1 in design view, and change the After_Update event procedures for the three combo boxes from Private Sub .... to Public Sub ... Save the changes.

'how do I run the after update event for cmbArea?

Use this: [Forms]![frm20MainStatus].cmbArea_AfterUpdate

So your code will look like this:

Private Sub btnStation_Click()
    Dim AreaLookUp As String, SubLookup as String 
    AreaLookUp = DLookup("[Area]", "[Qryxreference]", "[Station]='" & [Forms]![frm30UserData]![lstStation] & "'")
    [[Forms]![frm20MainStatus]![cmbArea] = AreaLookUp   ' That is correct
    [Forms]![frm20MainStatus].cmbArea_AfterUpdate
    '
    ' Same concept for the second combo
    '
     SubLookUp = DLookup("[Subarea]", "[Qryxreference]", "[Station]='" & [Forms]![frm30UserData]![lstStation] & "'")
    [Forms]![frm20MainStatus]![cmbSub] = SubLookUp
    [Forms]![frm20MainStatus].cmbSub_AfterUpdate
    '
    '   And the combo for Station
    '
    [Forms]![frm20MainStatus]![cmbStation] = '" & [Forms]![frm30UserData]![lstStation] & "'"    ' This wasn't quite right - missing single quote
    [Forms]![frm20MainStatus].cmbSub_AfterUpdate

end sub

That should be close. The trick is to make the After Update events of the combos in Form1 public, so they can be called from outside the form (as long as it is open).

[epardo87]

You're on the right track. First though, you have to make the event procedures in form1 accessible to Form2. Open Form1 in design view, and change the After_Update event procedures for the three combo boxes from Private Sub .... to Public Sub ... Save the changes.
Use this: [Forms]![frm20MainStatus].cmbArea_AfterUpdate

So your code will look like this:

Private Sub btnStation_Click()
    Dim AreaLookUp As String, SubLookup as String 
    AreaLookUp = DLookup("[Area]", "[Qryxreference]", "[Station]='" & [Forms]![frm30UserData]![lstStation] & "'")
    [[Forms]![frm20MainStatus]![cmbArea] = AreaLookUp   ' That is correct
    [Forms]![frm20MainStatus].cmbArea_AfterUpdate
    '
    ' Same concept for the second combo
    '
     SubLookUp = DLookup("[Subarea]", "[Qryxreference]", "[Station]='" & [Forms]![frm30UserData]![lstStation] & "'")
    [Forms]![frm20MainStatus]![cmbSub] = SubLookUp
    [Forms]![frm20MainStatus].cmbSub_AfterUpdate
    '
    '   And the combo for Station
    '
    [Forms]![frm20MainStatus]![cmbStation] = '" & [Forms]![frm30UserData]![lstStation] & "'"    ' This wasn't quite right - missing single quote
    [Forms]![frm20MainStatus].cmbSub_AfterUpdate

end sub

That should be close. The trick is to make the After Update events of the combos in Form1 public, so they can be called from outside the form (as long as it is open).

Thanks for your help John_G, it works great!

Best Regards!