Loop cascade forms

[epardo87]

Hello All,

I have 5 forms, all of them with a listbox in it, the selection in the listbox of one form, filters a query and opens up a new form with a listbox showing the filtered results of that query.

• frm20 filters Qry-Subarea and opens frm21 with a listbox showing the filtered results of Qry-Subarea
• frm21 filters Qry-Station and opens frm22 with a listbox showing the filtered results of Qry-Station
• frm22 filters Qry-Designations-1 and opens frm23 with a listbox showing the filtered results of Qry-Designations-1
• frm23 filters Qry-Designations-2 and opens frm30 with a listbox showing the filtered results of Qry-Designations-2

everything works fine until this point, the thing is that I need that when making the selection in the listbox of frm30 is when I need the loop to work, but I don't know how to do that, since frm23 has all data linked to the selection of frm22... any suggestions?

• frm30 should filter again Qry-Designations and open up frm23 with a listbox showing the filtered results of Qry-Designations

Thanks for your time!

[Micron]

The issue and what you have there is a bit vague.
If a listbox is showing the data based on the selection of some other list box (and that's all it is), why are you not just using the same form over and over again?
Whatever the action is that runs the event to do what you're doing could/should simply alter the recordsource of the form if it has one, and/or requery the listbox. Next selection, rinse and repeat...Trouble is, I can't tell for sure from your post if the recordsource of a form needs to be reset each time, or you simply need to add to the filter as you go. Since you keep using the keyword 'filter' I guess it's the latter. In that case, I figure the answer is to load the form, apply a filter based on the first selection, ADD the additional filter parts as you go.

Alternatively, it could also be that you ought to have 4 combo boxes on your form.
Choosing the first value filters the displayed records.
Choosing the second, filters by both; the 3rd by all 3 and so on.

[epardo87]

The issue and what you have there is a bit vague.
If a listbox is showing the data based on the selection of some other list box (and that's all it is), why are you not just using the same form over and over again?
Whatever the action is that runs the event to do what you're doing could/should simply alter the recordsource of the form if it has one, and/or requery the listbox. Next selection, rinse and repeat...Trouble is, I can't tell for sure from your post if the recordsource of a form needs to be reset each time, or you simply need to add to the filter as you go. Since you keep using the keyword 'filter' I guess it's the latter. In that case, I figure the answer is to load the form, apply a filter based on the first selection, ADD the additional filter parts as you go.

Alternatively, it could also be that you ought to have 4 combo boxes on your form.
Choosing the first value filters the displayed records.
Choosing the second, filters by both; the 3rd by all 3 and so on.

Thanks for your answer Micron,
It actually is a series of filters applied as you go through each form, I thought of making them in a single form but the thing is that, each form shows general data of each selection in that specific level before going to the next form, for example:
in the first form you select the area, and in each area you select, it is visualized who is the supervisor, what is the status of the area, what percentage of completion it has, etc. then, when you click on the "open" button, it opens up the "subarea" form, the listbox shows the available subareas in the selected area, the same way, you select one and information of that subarea is shown, you click the "open" button and then opens up the "station" form, showing list of stations contained in that subarea and showing information of each when selected, etc.

the filters work fine, the area form opens the subarea form with options filtered correctly, the subarea form opens the station form with options filtered correctly, the station form opens the operator form with data filtered correctly. the thing is that the station and the operator forms show the same data filtered differently: the station form shows the operators that can work on it, then you click in one of those operators and the operator form shows the stations that that operator can work on, so you can also click in another station that the operator has, and then the station form should open again but now showing the last station selected. my issue is that the station form, has it data filtered by the selection of the subarea form, but in this step I also need it to be updated by the operator form and have that possible any times required, because the user might be going through operators and stations over and over again.

[aytee111]

I agree with Micron, this is needlessly complicated and could easily be accomplished on one form with a subform which is requeried each time depending on the filter. Then you can have an option group - filter by station or by operator, giving them access to the right listbox of choices, and set the filters accordingly. With pages for different data lists/subforms.

Are you trying to have the same form open multiple times? This is not possible. Another way I read this is that your record source/filter is referencing a form that is no longer relevant/open (subarea). In that case, put the selection as a hidden field on a main form and use that in your filter instead.

[epardo87]

I agree with Micron, this is needlessly complicated and could easily be accomplished on one form with a subform which is requeried each time depending on the filter. Then you can have an option group - filter by station or by operator, giving them access to the right listbox of choices, and set the filters accordingly. With pages for different data lists/subforms.

Are you trying to have the same form open multiple times? This is not possible. Another way I read this is that your record source/filter is referencing a form that is no longer relevant/open (subarea). In that case, put the selection as a hidden field on a main form and use that in your filter instead.

Yes, actually I have been working on a main form that can contain as much information as possible without looking saturated. I will begin to work on that option you said about making the query be filtered by the main form box, let me give it a try and I'll let you know how it works. this is what I have so far:

• the first image is the main form, each of the first three comboboxes has options filtered by the previous one (the textboxes at the right will show data that I haven't linked yet).
• So the third listbox "Estación" will show in the listbox below the operators that can work in that station, in this example, there are three operators that can work in the "Hidrostática"
• if I select one operator and click the "ver operator" button, the window on second image opens, which shows the operator and the stations where that operator can work (as you can see, "Prieto" has the "Hidrostatica" and two stations more). so this window is the inverse of the last listbox and combobox combination.
• what I'm trying to do, is that if you hit another station in that window, it shows the inverse data. for example if you hit "Pilotos" I would like to see which operators can work in that station, and then if I hit another operator of those new options, I would like to see the other stations where he can work and so on...

___________________

[aytee111]

Would this work - two listboxes, one for stations and one for operators. After subarea, click on station in listbox to select it, operators-listbox shows operators for that station. Then select an operator and the reverse happens, the station listbox is populated with stations where that operator is.

It would entail changing/repopulating the SQL for the listboxes after each click. The other data (the empty boxes on the right) can be part of the SQL.

Very well presented, by the way!

[Micron]

why not just go back to frm20 and choose another station instead of creating a spider's web of open forms? So much simpler solution - less fiddling with code, recordsets, filters, open forms, additional controls....

[epardo87]

Would this work - two listboxes, one for stations and one for operators. After subarea, click on station in listbox to select it, operators-listbox shows operators for that station. Then select an operator and the reverse happens, the station listbox is populated with stations where that operator is.

It would entail changing/repopulating the SQL for the listboxes after each click. The other data (the empty boxes on the right) can be part of the SQL.

Very well presented, by the way!

Thank you!

So, I made a new query linking Area, Subarea and Station. I'm thinking about populating the three comboboxes in frm20 through the station selected in frm30, so it would lookup the area which contains that station and select it on the Area combo box, the same with the subarea and it might just copy the selection of frm30 to the combo box "station".

So I would have to apply a code "on click" in button in frm30... If you could help me with the code to select the area combobox that way, I'd appreciate it a lot!
pd. remember that the area combobox already has a query linked to its options, what I need is to select the one that corresponds to the station selected in frm 30

[epardo87]

why not just go back to frm20 and choose another station instead of creating a spider's web of open forms? So much simpler solution - less fiddling with code, recordsets, filters, open forms, additional controls....

well, this is going to be used by several people and I promised to design something very user friendly, easy to navigate through and I want to make it easier for them to work with this rather than using any other excel database that they might use currently. These are very stubborn people and is hard to make them change their way of working, but they are having serious problems with their administration of certifications of operators per station, I can't force them to use it, but if I deliver something very easy to use, to understand and efficient, I have a good chance of succeeding on this...

[epardo87]

I finally was able to make this work, this is 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 your support