Displaying records in hierarchal listview

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...



Who is Participating?
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.

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!

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
jg0069_2002Author Commented:
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....
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).
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.