Create Families from Volunteers User Interface

This is a sample of what you can do with the Treeview control and Microsoft Access.

Problem

There can be a number of volunteers from one family or household.  But printing and postage can be a major expense as well as finding volunteers to stuff envelopes.   How could I make creating families fast, efficient and as error free as possible?

Solution

(What you can't see due to the limitations of screen capture software is that currently the third line down, the volunteer named Smith is highlighted.   I had previously clicked on the Smith family.)

I created a family table to which I added records using this form.  I also updated the familyID foreign key on the volunteer table.  I then fed these to a treeview screen and with a bunch of mouse clicks allowed the user to create a family and put people under that family. I'm quite proud of myself for this UI. <smile> Even better the client just loved it.

I use three separate queries, duplicate last name, duplicate addresses and duplicate phone number for three separate forms as the basis for figuring out families.  The reason for address and phone number is to catch misspellings in last names. The app forms are frequently hand written.

I spent over a week programming it.  Much of that time was spent in figuring out how to do things in the nodelist.  I just kept on thinking up ways of making it more and more useful.  Now this same form would take me less than a day.

I have a three queries adding the families, then the volunteers belong to the families and finally the duplicated volunteer names and which are not part of a family.  I ran through these three queries adding the nodes to the treeview list.  If the volunteers are part of a family then they are a branch off the family record.  This is handled by putting the familyID in the Treeview node's relative field.

On the right hand side of the screen are two sets of name, address and phone numbers. The top one is for the currently selected family. The bottom one was for the currently selected volunteer. This information was expanded info of what you saw in the treeview list and was a confirmation of what you were about to do.

So if you clicked an volunteer the button to make this a family was unlocked. If you then clicked on make a family then it created a family and put the volunteer under the family. Then the family and the volunteer would appear on the RHS.   In the treeview control I would remove the volunteer node and add the volunteer as a child under the family.

Then you could click on an volunteer on the treeview, see the volunteer info on the RHS be updated and then click on "Add to Family."

The reason for having other virtually identical forms for address and phone number is to catch misspellings in last names. The app forms are frequently hand written and thus may be difficult to decipher.  (Not enough pharmacists are willing to volunteer as data entry clerks.  <smile>)

Notes

NodeIDs in the treeview collection must be alphabetic.  So I just prepended a "f" to the familyID and a "v" to the volunteerID.

Using the Microsoft Treeview control can cause many of the common ocx version problems when you go to distribute your application.   Best wishes on this problem.  I never got around to finding a third party control which worked in Access.  

A friend however has created this same effect using graphics and fields on a form and resizing them.   With lots and lots of code moving things about.  <shudder>

Treeview sample code and links

ACC97: Treeview Control Wizard for Microsoft Access 97 Available in Download Center - 163511
Basically Visual column - Out of my Tree (This is VB code but a cursory glance shows it to be close enough to be usable.)
Windows CE - eVB Treeview control and ImageList control
API: Change the ForeColor and Bold properties of a selected node  in Treeview

[ AccessMain ]