Displaying records in hierarchal listview
Posted on 2004-11-29
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...