Displaying records in hierarchal listview

Posted on 2004-11-29
Last Modified: 2009-09-12
Alright, heres an interesting question.

I'm developing a dynamic search interface for my MS Access database.  Basically the form is setup with a subform on the left that the user can scroll, that has a dozen or so combo boxes on it.  The right side of the form has a listview and the bottom has a toolbar.  The user selects his / her filter criteria from the combo boxes on the left and clicks "Search".  When the user clicks "Search", I open a recordset based and apply a filter and display the records in the listview.  Its a pretty simple concept, and is very user friendly.

Okay, that's the simple part.  The added twist comes in because I have multiple tables that I would like to search across.  There are 5 tables total and they are all related.  The tables that I have setup are shown below:

tblNotifications (Main Table)
tblNotifications_Items (Linked to tblNotifications on field NotificationNum) (1 to Many)
tblNotifications_Causes (Linked to tblNotifications_Items on field NotificationNum and ItemNum) (1 to Many)
tblNotifications_Activities (Linked to tblNotifications_Items on field NotificationNum and ItemNum) (1 to Many)
tblEquipment (Linked to tblNotifications on field EquipID) (Many to 1)

The added twist comes in when I'm trying to let the user specify filter criteria for the linked tables.  Basically what I would like to do is let the user specify whatever filter criteria they want, which is already setup on the subform on the left.  

I want to be able to list the records in a hierarchal listview of sorts.  I'm not really sure how I can do this, what I am looking for is something where a line item is listed for each record in tblNotifications and then there is a plus sign next to the line item that would display the related record in tblNotifications_Items and then there would be a plus sign for the related records in tblNotifications_Causes and tblNotifcations_Activities.  The link to tblEquipment is just a lookup type thing and its information is displayed on the line with the records from tblNotifications.  

I guess what I'm looking for is someway to do this using the listview control.  I remember seeing an example of a "hierarchal" listview in VB, but don't know if I can adapt it to VBA in MS Access.  If I can't do it using a listview, I'm sure there's got to be someway to do it with a subform that shows a datasheet where I can specify the recordsource for the datasheet with a SQL statement.

Any help or suggestions would be greatly... greatly... appreciated...



Question by:jg0069_2002
    LVL 58

    Accepted Solution

    Well, there is the Tree view control. It looks like the windows explorer and the like. But this displays only one string for each item. You will need a second pane for the details (like the list of files with dates, size, etc.).

    Come to think of it, you will probably need a second "details" pane whatever the solution you choose for the navigation...

    I have created a pseudo-tree view with a simple list box and a complex union query. Sub items were preceeded by spaces to show the indentation. If all types of records share certain fields, they could be displayed in columns next to that, but the number formatting of list boxes is bad.

    I suppost the same trick can be done with a continuous form... The first text box would be used to show the indented identifiers... Still, the query would be a monster to create, maintain, and tweak according to the filters...

    Hmm. I guess I can't really help. I would need to redo the analysis to have better ideas. As a general advice: keep things simple :)

    Good Luck!
    LVL 2

    Author Comment

    After a little bit of looking around, I ran across the MSHFlexGrid Control, which is the Microsoft Hierarchal FlexGrid Control.  I think this might be what I'm looking for.  I'm thinking there might be way to use this control to display my records.  What I'm trying to do is have a 3 tier structure as such:

    +  Data11    Data12    Data13    Data14   Data15  (First Level from Notification Table Collapsed)
    -   Data21    Data22    Data23    Data24   Data25   (First Level from Notification Table Expanded)
        +  Data2111   Data2112    Data2113    Data2114   Data2115  (First Sublevel from Notification Items Table - Collapsed)
        -   Data2121    Data2122    Data2123    Data2124   Data2125  (First Sublevel from Notification Items Table - Expanded)
                 Data212111     Data212112     Data212113     Data212114 ....  (Second Sublevel from Causes Table)
                 Data212121     Data212122     Data212123     Data212124 ...  (Second sublevel from Activities Table)
        +  Data2131    Data2132    Data2133    Data2134   Data2135  (First Sublevel from Notification Items Table - Collapsed)
    +  Data31    Data32    Data33    Data34   Data35  (First level from Notification Table Collapsed)

    So I guess I'm looking for a three three tier hierarchy where the first level is from the Notifications table.  The second level would be for Notification Items and then the third level would be for Causes and Activities that are linked to Items.

    Not sure if any of this helped... If the above structure is possible with the MSHFlexGrid control, and I think it is, how would one go about setting up the flexgrid and loading my records as shown above.  

    I know this is a tough one, but any help would be greatly appreciated....
    LVL 41

    Assisted Solution

    You could, I suppose use the abominations of nature known as subdatasheets - if your data at all levels is, or can be, displayed as a continuous subform, subdatasheets may do what you need (A2K and onwards, I think).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now