Dynamic form possible?

[CAD Fiend]

Hello,

Well, after an initial review of my database by my client, they have
completely changed their minds about how they want their form. As a
result, I'm having to re-think the whole process.

My Current Form (6 tabs):
- Owner, Property, Title, Docs, Queries, & Reports
- User is able to see (while navigating through the tabs) above in the
form : Owner Name, Address, Parcel, and SSN

The New Form (3 tabs):
TAB 1 - Owner, Property, Title, and Docs combined, to be "Dynamic"
TAB 2 - Queries
TAB 3 - Reports
- User is able to see (while navigating through the tabs) above in the
form : Owner Name, Address, Parcel, and SSN

The "Dynamic" requirement is due the fact that NOW my clients want to
cover ALL the possible ownership scenarios for this project, which is
now about 15 possible combinations. Some examples of ownerships are a
single owner, multiple owners, co-owners (like investors), 1/2 company -
1/2 private, and on and on.

My idea is to write an event procedure that, when the user clicks on TAB
1, an "OnClick" event, a procedure is called to load a dialog with 15
possible (of the aforementioned) options, and based on the user's
selection, loads the respective form. An example of the dialog would
be:

Form 1: Parcel is Separate Property (Male Lessor)
Form 2: Parcel is Separate (Female Lessor)
Form 3: Parcel is Separate (Multiple Lessors)
Form 4: Parcel is Community Property (Male Lessor)
Form 15: Parcel is under Power of Attorney
etc...
or "Cancel"
Lessor = Owner.. BTW

Questions:

1 - First off, am I going in the right direction? Should I do this
another way? Is there a more *simpler* way to do this? Instead of a
dialog, should I have a form to call any one of the 15 forms?

2 - Assuming I AM going in the right direction, then, would you do this
with Select Case, or what?

3 - If YES on #2, what will be the code for it? If NO on #2, what would
be the code for the NO answer?

4 - Originally, my form was from 1 table, now it will be 3 tables. They
will be tblOwner, tblParcel, and tblOwnerhips. Will I be able to get the
dynamic form to send data to ALL 3 of the tables? Or will I need to
create sub-tabs in the dynamic form to handle the data to each table?
I'm WAY lost on this one, so please bear with me!

5 - Let's say for ease of discussion, that if my question #4 is way off,
assume that I'll be working off of 1 table, if that helps you to explain
better the dynamic portion. I'll figure out someway to get everything
on 1 table, I guess.

I hope that I've explained this well enough, but if you need more info,
please let me know. I'm going to need to crunch this out over the
weekend (fun...). So I'm anxious to hear what you all have to say.

TIA.

Phil.

[BillCo]

> I'm WAY lost on this one, so please bear with me!

We now have that in common :)

1. It sounds like you are a little confused about how your table
relationships should look. If each owner can have more than one parcel,
then you need two tables: An owner table and a partner table with a
foreign key in it referring to the primary key of the owner table. The
easiest way to represent this on a form is to have a sub-form for
parcels, where the user can enter multiple parcels under each owner.

However, if there is only one parcel allowerd per owner, then the
easiest solution is to simply include parcel detials on the main table.

2. It seems that there are 15 different categories of parcel, am i
right? You could include a category field in the parcel table, and
depending on the value entered on the form, hide and show controls,
changing what the user is allowed to enter.

....I'm not sure I'm being any help here at all - I think I need more
info!

[CAD Fiend]

Bill, See my comments in-line.

BillCo wrote:

I'm WAY lost on this one, so please bear with me!
We now have that in common :)

1. It sounds like you are a little confused about how your table
relationships should look

Yes, I am.
. If each owner can have more than one parcel,
Yes, that is correct, one owner can own many parcels and one parcel can be
owned by many owners, so in effect, there is a many-to-many relationship

then you need two tables: An owner table and a partner table
What if you have many partners? How would I need to handle that?
with a
foreign key in it referring to the primary key of the owner table. The
easiest way to represent this on a form is to have a sub-form for
So would the sub-form be the child and the parent be the main form?

parcels, where the user can enter multiple parcels under each owner.

However, if there is only one parcel allowerd per owner
No, as I said earlier, it can be many parcels to one owner
, then the
easiest solution is to simply include parcel detials on the main table.

2. It seems that there are 15 different categories of parcel, am i
right?
Yes.
You could include a category field in the parcel table, and
depending on the value entered on the form, hide and show controls,
That's a good idea. How do I do that (form, hide and show controls)? Or can
you at least point me to where (and what keywords) to look for how to do
that?

changing what the user is allowed to enter.

...I'm not sure I'm being any help here at all
No, you have been a great help!
- I think I need more
info!

[BillCo]

glad I helped- you're reply cleared things up greatly - here's what you
need on the table relationship side of things:

1. tblOwner: OwnerID, Name, Address....
2. tblParcelOwnership: OwnerID, ParcelID
3. tblParcel: ParcelID, ParcelCategory, ParcelDescription....

relationship:

tblOwner tblParcelOwnership
OwnerID -1------- 8--OwnerID
tblParcel
ParcelID
--8----------1-ParcelID
Owners are completely seperate to parcels, but you can create a many to
many relationship between them using tblParcelOwnership. A parcel can
have many owners and an owner can have many parcels.

How to reflect this in terms of form design is another question... and
one which really depends on circumstances - but here's what I would do:

1. frmParcel
this woudl hold parcel details and a subform (sfrmP-O1) with
tblParcelOwnership as its recordsource. The ParcelID on sfrmP-O1 will
be updated automatically for each record. The OwnerID could be set by a
combo control, which lists all of the availible owners. The control
could have a hidden column with OwnerID which is bound to the OwnerID
datasource.
Now users can enter a parcel and associate any number of owners to it.

2. frmOwner
I would have a second form, frmOwner, which contains all the owner
details. You could have a subform, sfrmP-O2, with tblParcelOwnership as
its recordsource and have that work in a similar way, so users can also
work from the other end - associating parcels to owners.

Let me know how you get on!