I am a novice user of Access 2000.

I have created a query that creates a list of records. Each record includes
an associated field Root, Level1, Level2, and Level3. The record may,
starting with the Root, have all four fields populated with data or the
subsequent fields may be empty or blank. I also have a report for that
query.

Question: Is it possible to create a form that displays each record grouped
under its level in a collapsible tree structure that will allow a user to
select the records to be included in the report by clicking on a check box
displayed next to the root, each "branch" (Level1, Level2, Level3), and each
record (and any combination thereof)? For example, by clicking on Level2 all
records "in" Level2 would be on the report, as well as any other checked
records or level(s).

If this is possible, does it require complex coding or is there a template
or example somewhere that I could use to create this form?

Welcome to UA!

Treeviews are complex and they do require code. I have not seen one do what you are looking for, but I'm pretty sure it's possible. Do a search in the code archive for treeview and you will find at least 2 or 3 examples.

HTH,
Mehdi

Welcome to UA.

You are describing the kinds of things that can be done with a treeview. A treeview requires "real programming" although there are models you can follow so basic treeview operation can be accomplished without any "rocket science". I'd suggest starting by searching this form, as well as the Microsoft knowledge base, for the keyword Treeview. I built my treeview application with only the microsoft examples, but I have since noticed there are many posts on it here as well. Both here and at Microsoft you should be able to find a working example you can build upon, although I'm not sure I've seen one used for report parameters, so you'll likely need to extend the examples for that (there are lots of good posts here on filtering reports that you should be able to build upon.)

Good luck!

Welcome to Utter Access!

It does not sound to me like your data is normalized. You do not want those 4 fields in your table but they should be in a separate related table or tables, depending on their purpose. You will find some articles on normalization here, here and here

hth,
Jack

Hi Jack and others who have replied,

Those 4 fields were in fact part of a separate, related table in the db. I added them to this table just to make it easy for me to sort the records by those fields and then select the desired records by level.

Thanks to you all for the links you have provided. I wil read them and see if I can get to work on what I am describing.

Mark

I am thinking that the items should not be separate fields in a related table, but a single item... Something like:

tblLevels
LevelID (PK and autonumber)
MainTableID (FK)
LevelTypeID (FK)
Data

tblLevelTypeLookup
LevelTypeID (PK and Autonumber)
LevelType

tblLevelTypeLookup is table that will have Root, Level1, Level2 and Level3 for easy selection in the LevelID table...

I am not sure what you are up too, but this may give you some ideas or I could be dead wrong...

Jack

I found Microsoft Knowledge Base Article - 209898, ACC2000: Example Using TreeView Control Drag-and-Drop Capabilities. I decided to try it just to see how it looked. When I compile it I get an error message

Compile error: Variable not defined

"smallest = " is highlighted.

Cold someone try this and confirm if they get the same error?

I have used that demo successfully. I just looked at it and can't find "smallest" anywhere in it. Can you post the code you're having trouble with?

Stephen,

I'm sorry, but I didn't notice that the error occurred in the Module1 code. When I open and use the form it appears to be working without error.

Thank you for responding to my post.

Mark

You're most welcome!