Design Thoughts for Sub-Forms (1 Viewer)

[sproggit]

Hello everyone (and "Season's Greetings" !)... I'm spending some of the quiet time this holiday period with a little project to create a database from some pretty diverse (and inconsistently structured) data. Specifically, I'm in the process of setting up a simple Access DB that offers a consolidated, holistic "threat model", by combining a range of well-known industry threat frameworks, such as Mitre's ATT&CK, STRIDE, DREAD, LINDUNN, PASTA, and others.

If you look at ATT&CK, [sorry, I can't embed a link to the home page, but you can find it at "attack-dot-mitre-dot-org") you can see that it adopts a 3-tier hierarchical structure, with 14 Tactics displayed across the top and then a range of Techniques listed vertically under each. Lastly, some of the Techniques have sub-Techniques that are indicated by vertical grey bars to the right of the Technique. In other words, what we have here is a "tree" data structure.

Now, I've previously written (for a menu management requirement) a library of VBA that allows me to create and maintain such a data structure in a re-entrant table. I won't claim it's elegant or anything, but it works and has been robustly tested. However, the difference between my existing, working code and this new project is one of scale. With my menu management requirement, I could control the maximum number of leaf nodes to any given point in the tree and set an upper limit such that I could have one single Access Form with a well-ordered set of buttons on it. I could then control the visibility of these artifacts based on the number of menu options and, presto, I had a nice, neat, well-ordered form with clickable menu buttons on it.

What I'd like to do with my threat data is navigate with similar simplicity.

The problem is - as you can see from the ATTACK page mentioned above, the number of entities at each node in the tree is going to have a pretty wide range - and that's before we get to some of the more esoteric and not publicly available threat models, like IRAM2 from the Information Security Forum.

A set matrix of visible/invisible buttons isn't going to hack it this time.

The most obvious alternative would be to have a repeating sub-form, with a button and maybe an explanatory text field on each instance. But before I commit to code, I thought it sensible to come hear and invite suggestions from those with more experience of the GUI/Presentation side of Access. My skills are limited (so clear explanations would be appreciated!) but any suggestions as to alternative options would be very gratefully received.

For example, I considered coming up with a dynamically-rendered and re-sizing form, thus:-

• Query Tree table and ascertain the # of child records for the target node
• Divide by 2, rounding up, to allow for a 2-column structure
• Calculate a "form height" requirement based on a known structure of header data, plus ("n" x row height) where "n" is the number from the previous step and "row height" is the desired height of each line item (button, text, whatever)
• Render a bespoke, custom-sized form
• Populate with Command Buttons using e.g. the CreateControl language primitive

This looks to be technically possible, but excessively complex - and will result in a form that may require the user to scroll down below their current screen view to see all the buttons). In other words, it isn't clear to me that this would be any better than a sub-Form, which has the added advantage of allowing me to set and know the size of the parent Form.

The last thing I considered would be the sort of visual tree structure that is found in the Windows Device Manager (if you access Control Panel and set the "View by" option to either "Large" or "Small" Icons, then the applet in question is the one called "Device Manager"). Unfortunately, I've not been able to find any reference to accessing this visual functionality from VBA - and suspect that doing so would require e.g. VB, VC++ or similar.

So... style gurus... any thoughts please? Any tips on how to keep a sub-form clean and elegant?

Thanks in advance!

 

[CJ_London]

On my phone and difficult to follow your description but seems to me options are to use subforms with a crosstab query recordsource or subreports with multiple columns - so some data can be presented horizontally

there is a lot you can do to allow users to move subforms and other controls around with very little code which may be relevant and you don’t need much code to expand/contract subforms and other controls

and rather than using buttons, you can use other controls such as image to present data

 

[Pat Hartman]

Someone has built a tree control using VBA so it is native rather than an embedded ActiveX control. There is a free version and a pro version which is not free - here: Excel, Access: An MSForms (all VBA) treeview (jkp-ads.com)

One of our members has also built an all VBA treeview. I can't find it though so search here. I think it might have been @MajP

Other Options:
Microsoft TreeView Control Tutorial ~ LEARN MS-ACCESS TIPS AND TRICKS (msaccesstips.com)
A Tree-View Control Using an MS Access Table and Form - CodeProject

 

[MajP]

I have demoed all the capabilities of the MSCOMCTL ActiveX Treeview in the below thread and many others

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

www.access-programmers.co.uk

My class module is a wrapper to make working with the Treeview and Access far easier.
As mentioned there is also a version built in MSFORMS controls. I have no idea what You are talking about, but if a Treeview would work I can help.

 

[sproggit]

All, apologies for the lack of response.

My Father passed away on New Year's Eve. To say things have gone crazy as a result would be the understatement of the century. I wanted to come back and post my appreciation to everyone who has been kind enough to offer suggestions. I will be returning to this. I just don't know and can't say when. Thank you.

 

[MajP]

So sorry for your loss. I wish you and your family the best. When you do return to this if you think it is a Tree view then PM me directly. Most helpul may be to sketch some ideas using something simple like powerpoint or even Word. Maybe even some dummy forms / subforms in Access without any data. Worst case draw a picture and scan it.
Access forms are pretty limited compared to what can be done in something like Visual Studio. Sliding forms, dynamic resizing, MIDI forms, dynamic grids are pretty primitive in Access. Not sure of your coding background, but you can build a SQL Server backend with a VS front end. There are extremely robust features, but the learning curve is steep.