Dynamic Forms For Data Entry

I am attempting to improve an old paper-form process that we use for entering equipment in our Product database. In general, the current process is as follows:

1. When new equipment is purchased, basic information is entered into the database.
2. A separate paper form for each item is printed with the existing information and sent to the field engineer.
3. Each type of equipment has a different form because it requires different data.
4. After construction is completed, the updated information is written on the form and sent back to the main office for review/approval then entered into the database.

This is very archaic...Not only are we killing tons of trees, most specialists are getting buried by the paperwork!

What I would like to do is create a form or template that can be used with different spreadsheets. Instead of printing a separate form for each item, the specialists would dump the existing data for a site into a spreadsheet, attach the template/form, and send the file(s) to the field engineer (FE). When the FE opens the file, he would select the equipment type (using a dropdown box) and the appropriate form would pop up. He would then be able to edit the data for that piece of equipment or view the next item of the same equipment type. If he selects a different equipment type, a new form would be displayed. All changes would go into the spreadsheet and get sent back to the specialist. I’m hoping the review/approval can be done using the Track Changes feature in Excel. They then have a way to import the spreadsheet data back into our Custom database.

The basic implementation seems straight forward albeit a lot to learn. I have found many good examples in the forum; however, I’m having a problem figuring out how to handle the following:

1. Many different people create the spreadsheets and send them to different FE’s. When they create a new spreadsheet, how would my template/form get “attached”? Would they need to open a template file and add their data as a different sheet?

2. The equipment table in the database has approximately 95 fields; however, only about 20-30 of them are used for a particular type of equipment. I was thinking of using a unique list to create the form for each equipment type.

3. Since different specialists create the spreadsheets. The columns won’t always be in the same order. Is there an easy way to order columns or do you think I should use the ' find' feature to dynamically find each field? (Like I said, that may be 20-30 fields for each type of equipment, and there are currently 40 different equipment types.) Is there a limit on the number of user forms that can be in a project?

4. Finally, can you use the Track Changes feature when items are changed using VBA or a form?

The form seems like a good idea, but because the dynamic nature of the spreadsheets, the code gets rather complex.