Macro or VBA to auto-magically create/copy a form?

[Datech]

Short: Is it possible to have a macro or a VB script that creates a form based simply off of a record addition to a table?



Long: It sounds drastic, and might not be the right way to approach this, but here's the context:

We have "Owners" who report certain employees working on certain jobs. For input and verification purposes, we have a form that shows what employees/jobs combinations are under a certain Owner. All Owners are able to see (and edit, if necessary) any other Owner's form as well, so we don't need to worry about restrictions. I'll be using a switchboard form with a drop down list that shows all of these Owner forms (Daniel's Employees, Patrick's Employees, etc), with a button that takes them to that form so they can do their changes as needed. The Owners are specified in a table, and the one-to-many relationship is defined in the table that ties employees to projects.

What I want to know is if I add an owner to the Owners table, is there a way to automatically copy the layout of another Owner form (Daniel's Employees) and change it so that the new Owner's name is populated in the appropriate places (like the form title) and the functionality (pulling up NewOwner's employees instead of Daniel's) is matched to the new owner as well?

After typing all this it seems like the code for it would be a lot worse than just creating the individual forms myself. We won't have too many owners, but I guess I'm just asking in the spirit of automation. For those that know, a simple Yes or No will suffice. We'll work on the code later if it can be done. I'll be working on some of this tonight and tomorrow, so I should be able to provide a test DB if someone really wants it.

I've been able to put my nose through a few relevant chapters of The Missing Manual, and thanks for everyone's patience so far with some of my questions . I'm still against the clock with not enough experience and not enough time to learn. Such is employment...

[pbaldy]

Well, I won't say it can't be done, but it would be highly unusual. Why do you have different forms for each owner? You don't have separate tables, do you? Normally I'd expect to have one table with the owner's data, and filtering a single form to view the desired owner's data. There would likely be other tables for other things, but the point is you shouldn't have multiple tables for similar data.

[Datech]

Well, I won't say it can't be done, but it would be highly unusual. Why do you have different forms for each owner? You don't have separate tables, do you? Normally I'd expect to have one table with the owner's data, and filtering a single form to view the desired owner's data. There would likely be other tables for other things, but the point is you shouldn't have multiple tables for similar data.

I was reading Ch 14 on designing a navigation system, and thumbed back to Ch 13 about building advanced forms. I just need to read all the form chapters front to back before I go forward, probably. I don't think the automation would be worth it, even if the other stuff is the direction I want to head.

[pbaldy]

You haven't said what your table structure is, but I would head for the chapter on normalization. Properly designed tables are the foundation an application is built on, and should be the first thing done.