Passing Context Between Forms

[sproggit]

Hello everyone... I'm trying to do something with forms and sub-forms that I think *should* be possible, but I'm struggling with the implementation. I'll try and describe what I'm trying to achieve in 2 ways - a high level "what I'm trying to show" and then "how I'm trying to get there". Whilst obviously I'm primarily interested in a bit of help with "how I'm trying to get there", I concede that there may [likely are] more elegant, simpler ways. So I'd be incredibly grateful for any thoughts on how I might go about this...

Thanks in advance.

What I'm trying to do is build a "visual library" based on a set of international technology control standards. I'm starting with ISACA COBIT, NIST SP800-53, ISO 27001/2 and something called the "Information Security Forum Standard of Good Practice". My hope is to have a way of producing a simple-to-navigate visual tool that makes it trivially easy to navigate across these sources by showing how the different entities "map" to eachother.

Each of these industry standards, whilst offered in book form, actually has a mature, standardised structure. COBIT, for instance, has 5 top-level Domains; each Domain has multiple Objectives; each Objective has multiple Processes; each Process consists of multiple Activities. It's been really easy to represent this in a relational model and to write some basic queries to allow retrieval of the data in the ways I need.

Now I'm looking at representing this visually on-screen. Please take a look at the [very crude] prototype layout, attached. Walking you round that form:-
- The grey horizontal bar and second box are for labels and will be filled in when this is working...
- The lighter blue buttons show the 5 Domains of the data model...
- The darker blue/purple buttons show that the first of the 5 Domains has 5 children [the reason there is space from the 5 children to the tables lower down is that some functions have many more children...].
- Lastly, we have what are currently, on this prototype, a pair of list boxes.

When an operator click a "Domain" button, the screen is populated with the child Objectives associated with that Domain. Click on an Objective, and the left-most list-box is populated with the child Objectives. Click on an Objective, and the larger list-box on the right becomes visible and is populated with discrete activity steps associated with the Objective. So far, so simples, right?

Now to the problems...

What I'd like to be able to do is have the same sort of click-on-a-line-item functionality for this second, right-hand list box. Except when you click on one of these items, I want the list box on the right to disappear and be replaced by a detailed break-down of the element, showing you information about that specific line item, for example, where it maps across to other Industry Standards or, more importantly, to a bespoke set of technology controls that I'm writing.

Now, I've got experience of building a form with a single "sub-form" design slot, but be able to "swap in" two or more different sub-forms in to the same space on the parent form. Armed with that knowledge, I decided to replace the second, right-hand list-box with a sub-form. So now, when I click on a list item on the left, instead of the right-hand *list box* becoming visible, I load a sub-form. Perfect - just what I want.

Just not quite everything... What I'd like to do is pass context to this sub-form. I can determine which element in the list in the left list-box was clicked... and I can use that information to populate a second list-box - when it's on the same form. What I would like to do here, however, is pass that data to a sub-form. That's Problem No. 1...

Problem No. 2 is, imagine that I've solved Problem No. 1. The screen is rendered pretty much as you see it now [with the right-hand list being in a sub-Form. Now I want to click on one of the elements on the sub-form, and I want that sub-form to vanish, be replaced by a second sub-Form, which will lay out a bunch of contextual information. On that second sub-form, I want a "Back" button which will restore my first sub-form, in the relevant context [i.e. populated with the same data].


Where I've got to - and I'm just exploring how to make this work - is to use a combination of Global Variables and "On Load" events. When a user clicks on a List item in the left-hand list, I can trigger a Method... that would enable me to identify and harvest details of which item in the list was clicked. I could then load that value in to a Global Variable and in the same List Event Method I could trigger steps to load the "List Box Form" on the right. That Form could have an "On Load" Event which retrieves the Global variable and uses that to populate it's data set...

I could then employ the same logic in the right-hand form such that when a user clicks an item in this second list, the "On Click" event will hide the current sub-Form, make my second Sub-Form Visible and with that pass another global variable to my second sub-Form that will help it to load and display the relevant details.



If you've managed to make it all the way to here, then apart from "thank you!" I suspect you might be thinking, "hang on, why are you trying to do all this by hand when you could use things like nested Continuous Sub-Forms" (or other solutions). Well, I did experiment with continuous sub-forms, but I found a list box to be *much* more compact and un-cluttered; there's no waste screen real estate, which will be relevant. Also, in part, I'm trying to keep this neat, elegant and clutter free.

So anyway... very interested in absorbing some collective wisdom; tips on things to look at; danger points to avoid; that sort of thing. Suggestions would be most gratefully received.

Thank you.

 

[The_Doc_Man]

For your problem #1, consider a Tab Control and put different sub-forms on each tab, then based on what you click, force the appropriate Tab control to become active (hiding the other things). Technically the thing you wanted to go away doesn't go away - but it DOES get hidden since Tab controls don't have to be transparent.

Then your problem #2 simple becomes (a) populating the 2nd sub-form and then (b) activating the appropriate Tab control, thus hiding earlier stuff. And the "Back" button just requires you to remember the order in which you visited the Tab's panels.

 

[MajP]

There are lots of ways to do this. The question is really the aesthetics. I was thinking a tab as well. But you could also change the source object of the second subform from "Steps" to "Step Details" or you can show and hide 2 subforms.

Tabs would be the simplest. I am not sure if you even have to hide the tab. These seem to be Cascading lists. You click an Objective and it filters the list of Steps in the second subform. If you click on a step you want to get the filtered details for that step. You could just add a tab control with 2 tabs: "Steps" and "Step Detail". You click in the Objective list and it would basically set focus to the first tab "Steps" and filter like you have above. Then you click on a Step and it sets focus to the "Step Detail" tab and gives you the details for the Step. You can simply go back by selecting the "Steps" tab or you can add a button on the "Step Detail" tab which simply sets focus to the "Steps" tab. Not much improvement then manually picking the "Steps" tab.

However, If real estate permits why not have three subforms. Left is Objecive. Right top is "Steps", and below that is "Step Details". This way you can see the drilling down.

 

[MajP]

The three subform idea would be like this demo. I pick janet leverling, then get her order, and if I pick an order I get its details.

 

[Mike Krailo]

MajP, do you have a link to your demo for that screenshot?

 

[MajP]

There is lots of ways to do it. This demo does it by using the current event of the subform. When you select an Order in the subform it sets the value of a hidden textbox on the main form equal to the orderid. So if you click on 10309 the value of the textbox is 10309. The second subform is linked to that textbox. You can link subforms to controls not jus a main form record.

You can actually directly link two subforms and in fact link two subforms on different forms. You just got to get the LinkMaster and LinkChild syntax correct. I find this way is simpler, I can never get the syntax correct.

 

[MajP]

FYI I use conditional formatting on the second subform to simulate selecting a row.

 

[Mike Krailo]

There is lots of ways to do it. This demo does it by using the current event of the subform. When you select an Order in the subform it sets the value of a hidden textbox on the main form equal to the orderid. So if you click on 10309 the value of the textbox is 10309. The second subform is linked to that textbox. You can link subforms to controls not jus a main form record.

Ah, I see now. I actually have a project that uses that same general idea. It's definitely a great way to display the data being able to see all three at the same time. Thanks for the links.

 

[MajP]

So you can link subforms directly, but the hidden textbox actually has an advantage.

In my example I want to link the second subform to the first subform (SubFrmOrders)by OrderID. In the Second subform the Master/Child would then look like
Master: [subformOrders].form![OrderID] (no parent form reference needed)
Child: [OrderID]

If you do this it seems to work the first time but not after that. So in the first subform's current event if you put a requery of the second it will work.

Private Sub Form_Current()
  On Error Resume Next
  Me.Parent.subformOrderDetails.Form.Requery
End Sub

The hidden text box takes care of the requery, and it is easier to write in the property window.

 

[Pat Hartman]

I'm going to vote for the Tab concept. Here's a sample that is similar to what yo are looking for. Notice how you can give names next to the navigation bar so clarify what form you are navigating.

Here's a second form. The above form was used to define the process. The next form shows a project tasks and their status.

 

[sproggit]

Firstly - and most importantly - I'd like to thank everyone not only for replying, but for doing so with such useful examples. It's been educational and I am incredibly grateful for the time and effort you have invested!

Secondly - and please don't hate on me - I might have come up with "yet another way". I have something that is tantalisingly close to working... but, well, let me explain with a couple of screen grabs and some code snippets... in the hope that you can spot the idiot mistake I've made...

If you take a look at the two screen shots below, the first [on the left] shows how the application "switches in" to the "APO" function if a user clicks across the row of cyan buttons across the top of the navigation block on the form... If you click on EDM or BAI, then the darker blue buttons beneath will simply swap, to show the different elements each of those Domains contain. So far, so good... Click on "APO" and then click on "APO01" and now the list on the left, which is on the same form, populates with matching records from the first table. That works beautifully.

Now click on any one of the rows in the listbox on the left... That, of course, invokes, the "Private Sub listProcess_Click()" method (where "listProcess" is the name of the ListBox on the left of the form). The code in that method is as follows:-

Private Sub listProcess_Click()
Me.frmCOBITDomainAPODetail.Visible = True
Call Form_frmCOBITDomainAPODetail.Data_Reset("SELECT COBIT_Process.ProcessRef, COBIT_ProcessActivity.ActivityRef, COBIT_ProcessActivity.ActivityDescription FROM COBIT_Process INNER JOIN COBIT_ProcessActivity ON COBIT_Process.ProcessID = COBIT_ProcessActivity.ActivityProcess WHERE (((COBIT_Process.ProcessRef)=" & Chr(34) & Me.listProcess.Column(0) & Chr(34) & ")) ORDER BY COBIT_ProcessActivity.ActivityRef, COBIT_ProcessActivity.ActivityDispSeq;")
End Sub

... and of course, you can see that I'm using "Call Form_" to call a method on my sub-Form. That Method looks like this:-

Public Sub Data_Reset(NewParam As String)
ListActivitiesDetail.RowSource = NewParam
End Sub

I've made it "Public" so that it becomes accessible and I've kept it as simple as possible for now. The second image shows the result that I'm getting. Instead of my code basically re-setting the way that data in the second ListBox gets populated, I have managed to pass my SQL Statements to the ListBox as rows of data...

The weird thing is, I use the exact same SQL structure to re-define the contents of my first (left) ListBox when any of the buttons across the top of the form are populate. I use variations on this code, which works:-

Private Sub btnAPO14_Click()
listProcess.RowSource = "SELECT COBIT_Process.ProcessRef, COBIT_Process.ProcessName FROM COBIT_Process WHERE (((COBIT_Process.ProcessObjective)=""APO14""));"
End Sub

The contents of my SQL String here are not relevant because they apply to a different ListBox, but the use of the basic Syntax

{ListBoxName}.{RowSource} = "SQL String"

seems to work just fine. Like an idiot, I thought that I could re-use the same approach to pass a new SQL string to a variable in a different form. What's happened is that the ListBox on the "referenced" form is choosing to interpret the string I'm passing in a slightly different way - treating it as row source data rather than a SQL statement.

I'm guessing that I need an extra set of quotes and/or parentheses and/or a slightly different syntax wrapper to manage handing the data across. Or, I wonder if that doesn't work, if I might be able to simply load the SQL statement into a pre-declared Global String variable (I can see you all shuddering in horror...) and then I can simply invoke the remote method without parameters, but have that remote method retrieve the String from the Global variable.

Obviously (your other suggestions notwithstanding), the more elegant solution would be to figure out the correct parameter syntax and go from there.

If anyone can suggest where I've gone wrong in wrapping my SQL, I'd be incredibly grateful. If not, then I'd be equally grateful if anyone can direct me to any resource that might help me triage my suspect code.

Lastly - and thanks for reading this far - I do hope that my experimentation with another approach doesn't come across as me being rude or ignoring the excellent advice. Ultimately, my decision to experiment and try to come up with this is purely down to aesthetics and the knowledge that I'm going to have limited screen real estate to host the data as this application is built out. (And yes, obviously I'm sharing quick-and-dirty forms at this stage in things. To borrow from Eric Raymond ("The Art of Unix Programming") - "Prototype then Polish". I need to get the basic functionality nailed down first!)...

What I like about using the ListBox approach (as opposed to subForms) is that there is significantly less border/formatting space required; the resultant on-screen object can have invisible borders - in fact it looks simply like a ListBox on the original form.

"Wait! What?" you exclaim... "You're massively over-complicating this! Just have two list-boxes!"

The reason is, however, is that what I can actually do is have two sub-forms, with exactly the same dimensions, then switch them into what you see on the screen, seamlessly. If you look at the second image, you can see that I have 2 list-boxes showing, even though the one on the right is in a sub-form. In the final version of this, when you click on an item in the ListBox on the right side, that entire sub-Form will be replaced by a completely different one, which will display all the detail associated with the second selected row item. But I can lay this out in a more-or-less traditional form structure, using the real-estate to make it visually easy to follow. I can add a "back" button on that second form, to revert to the "navigation mode" and allow the user to work backwards if they wish...

It's that neat/clean/slick operation that I'm aiming for. Likely an unorthodox approach, but I hope very effective when used.

Thanks again everyone.

 

[MajP]

Your design should work, it just needs some error checking.
I always set a variable so I can debug.

Dim strSql as string
strSql = "SELECT COBIT_Process.ProcessRef, COBIT_ProcessActivity.ActivityRef, COBIT_ProcessActivity.ActivityDescription FROM COBIT_Process INNER JOIN COBIT_ProcessActivity ON COBIT_Process.ProcessID = COBIT_ProcessActivity.ActivityProcess WHERE (((COBIT_Process.ProcessRef)=" & Chr(34) & Me.listProcess.Column(0) & Chr(34) & ")) ORDER BY COBIT_ProcessActivity.ActivityRef, COBIT_ProcessActivity.ActivityDispSeq"
debug.print strSql
'Take a look at the string and see if it is what is expected
Call Form_frmCOBITDomainAPODetail.Data_Reset(StrSql)

I would get rid of the semicolon in the strSql. If this is going to a listbox that may cause problems it will see that as a column seperator

I think an easier way is simply to set the rowsource from your calling form. No need to call a procedure.

Dim strSql as string
strSql = "SELECT COBIT_Process.ProcessRef, COBIT_ProcessActivity.ActivityRef, COBIT_ProcessActivity.ActivityDescription FROM COBIT_Process INNER JOIN COBIT_ProcessActivity ON COBIT_Process.ProcessID = COBIT_ProcessActivity.ActivityProcess WHERE (((COBIT_Process.ProcessRef)=" & Chr(34) & Me.listProcess.Column(0) & Chr(34) & ")) ORDER BY COBIT_ProcessActivity.ActivityRef, COBIT_ProcessActivity.ActivityDispSeq"
debug.print strSql
me.Parent.SecondSubFormControlName.Form.listActivitiesDetail.rowsource = strSql

However if you are using listboxes then I am confused on why any subforms.. You can have multiple listboxes and show, hide, size, and move dynamically to get what you. Or you can have one listbox and change the rowsource and even the number of columns.
In fact you are actually simplifying not complicating. Having multiple listboxes that are sized, hidden, shown, moved, and repopulated is easier to reference than any subforms.

 

[Pat Hartman]

What I like about using the ListBox approach (as opposed to subForms) is that there is significantly less border/formatting space required; the resultant on-screen object can have invisible borders - in fact it looks simply like a ListBox on the original form.

You can make subforms disappear into the background also. You just have to play with the border properties.

 

[MajP]

Here is a demo, I think doing what you say.
Again there are lots of ways to do something like this. You can hide and show listboxes in the same location. Or it could be just one listbox that you change the rowsource. Coding multi column listboxes can be a pain trying to get the formatting correct for each column to look good. So I did it with 3 seperate listboxes. Instead of hiding and showing the listboxes I but them on a tabcontrol, but I set the format to be no tabs. This made it a little easier to show and hide, because you just change the value of the tab control which changes the page. In this case since there are no visible tabs it appears as if a listbox is shown or hidden

Click on an Employee it shows the tab to page 0.

click on an Order and it changes the page (appearing as if a new listbox is shown)

Here is the code

Private Sub cmdBack_Click()
  Me.tabOrders = 0
  Me.tabOrders.Pages(0).SetFocus
  Me.cmdBack.Visible = False
End Sub

Private Sub Form_Load()
  With Me.lstOrderDetails
    .Left = Me.lstOrders.Left
    .Height = Me.lstOrders.Height
    .Width = Me.lstOrders.Width
    .Top = Me.lstOrders.Top
  End With
End Sub

Private Sub lstEmployees_Click()
  SetOrdersRowsource
End Sub
Private Sub SetOrdersRowsource()
  Dim empID As Long
  Dim strSql As String
  If Not IsNull(Me.lstEmployees) Then
    empID = Me.lstEmployees
    strSql = "Select * from QryOrders where Employeeid = " & empID
    Me.lstOrders.RowSource = strSql
    Me.tabOrders.Visible = True
    Me.tabOrders = 0
    Me.cmdBack.Visible = False
  End If
End Sub

Private Sub lstOrders_Click()
  SetOrdersDetailRowsource
End Sub
Private Sub SetOrdersDetailRowsource()
  Dim OrderID As Long
  Dim strSql As String
  If Not IsNull(Me.lstOrders) Then
    OrderID = Me.lstOrders
    strSql = "Select * from QryOrderDetails where Orderid = " & OrderID
    Debug.Print strSql
    Me.lstOrderDetails.RowSource = strSql
    Me.tabOrders.Value = 1
    Me.cmdBack.Visible = True
  End If
End Sub

FYI, It is impossible to work with a tab control without tabs. You need to build it with the tabs and then remove after design.

 

[sproggit]

MajP, thanks again for the responses and even more so for the example answer. The mock-up you offer is pretty much exactly what I'm looking to do, except on the third screen view, where you have the "Back to Orders" button... Here I don't plan to use yet another formatted ListBox, but a regular form, with fields laid out so that they show numerous different elements of data to the user. That's the reason why I was leaning in the direction of swapping between two sub-Forms rather than just swapping between ListBox Objects.

But there's nothing in that difference which invalidates your example. In fact, further up the thread, the idea of using a tabbed form has also taken my interest and it's something I plan to explore as soon as time permits.

As with any long-lived and feature-rich programming environment, there are many ways to achieve any given end result, so it's been education to come and take advice here. Every new technique learned is like another tool in the toolbox - always worth having to hand for when those out-of-box problems come along...

Thank you

 

[MajP]

So this is demoed using a subform on the other tab. I am still using the same linking idea that I demoed in the very first example. I put a hidden textbox. You can spend the time passing a sql string and roll your own link, but this requires hardly any code to link subforms.

I am getting confused where you want subforms and where you want listboxes. I think the design you want is a permanently visible list (listbox or subform). Lets call that L1. When you select something in L1 it brings up list two to the right (L2). If you click in L2 at the same location and same size it brings up L3 with a button to bet back to L2.
So L2 and L3 can be on tabs like I show. You can choose to show the Tabs of a tabcontrol and the user can just select between L2 and L3. Or do what I did and hide the tabs. But if I am using subforms I am using the linking trick I showed in the very first example. That will save a lot of code.

If L2 and L3 are subforms, I think I would still go with the tab control it makes the showing and hiding really easy. I would go with the hidden textbox linking. If you want you can get rid of the tabcontrol and just have a single subform control. Then you just change the source object of the subform control. You can still do the easy textbox link.

 

[sproggit]

Hello everyone, I wanted to come back and offer a follow-up to the post I made on Wednesday at 12:34pm.

In that post, you can see that I'd got the basics reasonably straight, but when my second ListBox appeared, instead of showing the data I was expecting, it actually showed the SQL that I was passing between the forms. I wrestled with this for a while, using MsgBox statements to show the text/content of the SQL statement before and after being passed via the Call, but I couldn't spot anything wrong.

Decidede I couldn't "see the wood for the trees", gave up on it for a bit. Went and played Destiny 2 on the PS4 for a bit.

Came back... and fell over the problem right away.

I'm bringing in a sub-form and on that form there is a single object, a ListBox, that I want to populate with the 3 tier of nested data, as identified by the SQL Statement I'm passing. The problem was, somehow I had changed or reset the other ListBox properties for this second ListBox.

Instead of the "Row Source Type" field containing the value "Table/Query", I had accidentally and unknowingly changed it to "Value List".

It was just a sheer fluke that my SQL contained some double-quote and comma characters and that Access did it's best to interpret what I had sent it, giving me a broken set of SQL in the ListBox rows instead of the source data I expected. There was never anything wrong with the SQL, which I kinda knew seeing as I had it working on a previous and simpler prototyle of this form.


So I've now got things set up so that I can jump between Functions using the top-level buttons on my Form, re-program and hide/display the second-level controls; adjust the contents of a first ListBox based on the second-level control being selected... and when a user clicks on any item in the first ListBox, the form now reveals the sub-Form and second ListBox, with the correct sub-Set of data filtered and presented.

All that's left to do now is write a little bit more code in the _Click even for the second ListBox, which will hide that sub-Form, display the alternate, and populate that with the underlying detail I want to show.


I just wanted to come back, own up to the stupidity of accidentally switched Row Source Type parameters [just in case anyone else stumbles over this thread], but most importantly thank MajP and everyone else who contributed.

Very much appreciated.

Thank you.

 

[MajP]

As I said there should be plenty of different ways to do this. You can show and hide lists, use a tab, show and hide subforms, change one subform source object. Each has different levels of complexity. I found the tab the least amount of code, but still requires some finesse. However, going back to your original design. You do not have to pass a sql string to the subform and then have the subform change the rowsource. You can simplify that by having the calling code that shows the second subform to also set the rowsource.
It is like me handing the remote to my wife and asking her to change to channel 123. Lot easier and safer for me to do it myself.