How to best handle the spaghetti created by drilldowns

I have an application I created in Access that is very different than I have done in the past. You are accessing the same data from several different beginning spots with drilldowns. I discovered that I needed to save the record before the drilldown, because otherwise you are editing the same record in two different places, which is obviously not a good thing.
However, I am wondering how others handle this kind of complex setup where there are multiple paths to get to the same place. I work for a General Contractor, so as an example, you might start from the client screen, and drilldown from one of the child projects into the form where you are seeing the project as parent with all the subcontractors assigned to the job as children. Then you might want to see all of the projects the subcontractor is associated with, so you drilldown to the form where the subcontractor is parent and projects are the children. You might get interested in one of the projects on that screen and drilldown into the projects/subcontractor form where the new project is parent.
What I ended up doing is making all the forms modal, but that becomes cumbersome, because maybe in the midst of this, something comes up and I need to go to the employee form.
How can I do this in a way that makes sense without having any issues with data integrity? I am considering hiding the form that was drilled down from and them displaying it when you close the next form, as I have done in other applications, but in this one, the drilldown could go so deep that I would have to have  a way to track the drilldowns in from start to finish. One thing I would also want is the ability to just close the whole chain out at any point so I don't have to step through each step in the chain sequentially. The other complication of this is that you may have multiple chains going in different directions, i.e., starting down the path of two or three different projects. It has the potential to get really messy. Also, I am reusing forms. Will that cause me any issues if I am editing in a form at one place on the chain and then use it again later in the chain in a different parent form?
 I worked with an application a few years back that did all of this with drilldowns. I wish I could see it again now to play with it and see how they handle different things.
I would love to hear how others have handled this type of application and any additional insights you might have.
Thanks so much for any input you can give me!
Kim HowardAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I hide each form as I drill down.  The calling form passes its own name to the called form using OpenArgs.  That way the called form knows which form to return to when it closes.  I might have a half dozen forms open but hidden.  The only problem I've run into is that I can't allow for multiple paths to the same form at the same time.  I don't know how this works when you use multiple instances of the same form.  This method also does not allow for a "close all" option although you could add that button and build the logic to loop through the open forms collection and close everything except the Login (which always stays open and hidden) and the menu which also always stays open but is hidden when another form is opened.  I hide the menu because users get very confused when multiple forms are opened and they have the bad habit of going back to the menu to reopen a form and that causes a problem unless you always close and then open.  Opening an open form, does not pass in new arguments so the form just reappears showing the old data.

If you want something as sophisticated as what you are suggesting, you are going to have to create a table *stack" to record the form and the ID of the record you were looking at.  This of course becomes way too complicated if you are working with subforms so I have not even attempted it.  Let us know how it works out for you.
Dale FyeOwner, Developing Solutions LLCCommented:
most of my applications which allow drilling down on multiple records also call the form with an OpenArg value which points back to the calling form, and hides the form it was drilled into from.  You could keep those forms open as popup, not modal, and make sure you open them up with WindowType not acDialog.  When you use acDialog to open the form, and then subsequently hide that form, the code that was used to open the form originally will continue, so you have to be careful about putting code in the modules below the OpenForm method.

I've not had many clients that wanted to be able to open multiple instance of the same form, but with different data, but with multiple monitors becoming quite common, and the increase in available screen realestate, I can see this as a very desirable situation.  You might want to look at Allen Browne's article on working with multiple instances of the same form.  

If all you are doing is drilling down to review data, that is one thing, but if you are going to want to edit data on one form and then drill down to another level (via another form), you need to ensure that you save the current changes before you open the subsequent form.  You will also need to make sure that you either refresh or requery forms when the focus returns to them, after editing data in a subsequent form.  You might do this by keeping track of a global variable or tempvar and using that as a flag to requery/refresh the calling form in the current form has had records added, edited, or deleted.  I would normally do this in the OnClose event of the drill-down forms (goes back to the issue of having code below the OpenForm method mentioned above).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John TsioumprisSoftware & Systems EngineerCommented:
Just a simple question...does your forms have large "screen estate" ...(lot of controls...occupy a big chunk of screen)...
If not maybe you can make everything subforms and host them under a fake master form and make linking using unbound textboxes that carry the linking...
If you gave us a bird's eye view maybe we could help more
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Kim HowardAuthor Commented:
I  want to apologize for disappearing and never responding to any of you. Life got crazy and I just could not get back to my database work.
I actually work as an Office Assistant for a General Contractor and am creating the database when I have *spare* time and can stay home to work on it. It is a really fun project, but took a back seat to everything else when we got extremely busy.
I did read all of your comments when you first answered and appreciate your input. My plan was to mull them over before I responded and BAM!, work got crazy busy. Based on your answers and my time mulling, I finally decided to just make everything modal for now, because I have to get something out and don't have time for anything more complex. They have finally given me a couple of days to work on it again, so I am hoping to have an initial roll-out by Monday.
Kim HowardAuthor Commented:
I realized that this was really not doable without a lot of complex programming that I don't have time for at this juncture.
My suggestion required no complex programming.  In the unload event of the open form, you would need an If statement to reference the OpenArgs to open the form that called the one that is closing.
Kim HowardAuthor Commented:
I inherited a database a couple of years ago that worked similarly to what you are describing. What is the advantage of using that over a modal form?
I am also reusing sub-forms, and I would think that I would not be allowed to have them open on multiple forms, even if they are hidden. I may be wrong, though, because I can use the same sub form on 2 different tabs of a form.
Model forms interfere when you want to preview a report.

I'm sure the others will be happy to help you from now on.
Kim HowardAuthor Commented:
I have got a report on one of my modal forms and it seems to be previewing  just fine. Is it possible that this was fixed in later versions of Access? I am using 2016.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.