Creating a form for 4 tables linked with one-to-one relationships?

[adwest]

Forgive me if this is a foolish question. I "play" in Access and have only created a few rather simple relational databases. My knowledge and experience is limited. I have no formal training, just picked up a few books and have gone from there... I'm not sure how to relate my question using the accepted shorthand, so I'm afraid this will be a narrative description.

I am using Access 2003. The database is for property management. I have about 12 tables. I know one-to-one relationsips are not normally recommended. In my case I "thought" it made sense to break down the information for organization purposes. And because not every record in what I consider the parent table will have a matching record in the child table(s).

In layman's language, my first table is general property info (called Property Info). It contains about 40 fields of information about the property such as address, city, state, zip, date available, rental term, pets allowed?, smoking allowed?, and many more similar fields. The actual fields relevant to my question are: PropertyID (pk), a lookup to an Owners table, and street address.

My second table contains information about any utilities and services included in the rent. There are about 70 fields in this table. Basically, each field is a different utility/service (yes/no .. there are about 35 fields each with different possible utilities and services that may be associated with any given property), service/utility provider (if yes), Service Contract? (y/n), Contract Expires When? (date), and other relevant information. The actual fields relevant to my question are: ID (PK - autonumber), PropertyID ( FK - used to creat the one-to-one relationship with my first table), all other fields pertain to the 35 service possibilities.

My third table is similar to the second but contains information about the included furniture, appliances and systems, and related warranty and service contract information. This table has close to 70 fields. Again, the possible list of included items are Y/N fields, then if set to yes the warranty and service contract information is entered. The fields relevant to my questions are: ID (PK -autonumber), PropertyId (FK- used to create the one-to-one relationship with the first table), and all other fields pertain to the various inclusions.

My fourth table is similar to the second and third, but contains special clause information such as legal info, deed restrictions, condo restictions, assoc. restrictions, and homeowner imposed restrictions. There are about 80 fields in this table. The relevant fields are: ID (PK-autonumber), Property ID (FK- used to create the one-to-one relationship), and all other fields identify each special clause and related information.

Ultimately the 4 tables together contain all relevant property information we need. Some properties may include several utilites, appliances, etc while others may include nothing extra. Soem may have several restictions, others may only have one or two. That is why I set up the tables this way.

What I am tring to do: I want to create a data entry form that combines several fields from all these table to mirror the format of paper forms in use. I tried to create a main form with 3 sub forms, but could not get it to tab properly through the main form (about half the fields), then jump to and through the first sub form, then to and through the second subform, etc ending with the remaining fields in the main form. I could set tab orders in each individual form, but the user would have to stop and click on each subform to activate it so they could enter the appropriate information. Additionally, this layout created a long entry form where scrolling could be necessary.

So I thought a tabbed form would work better. I could organize relevant information on each tab. But I can only get the fields from my first table to be available in the field list when in design view. I have not tried creating a form from a query, because one of the books discouraged that (can't remember why now). However, is that what I should be doing??? Or is it possible to do a subform using the tabbed pages format? Or am I going about this whole thing the wrong way?? I've spent days working on this in my spare time (not!) trying to figure out how to do this one form. I just can't figure out the best way to do it, so I am turning to the Experts here!

This newbie really appreciates any help or suggestions you can offer and thanks you in advance for your patience with my questions and posting style!

[MMcCarthy]

My advice would be to use the tab Pages object. Bind the main form to the first property table an put the PropertyID Control on the form (You can hide it). Then create a subform for all four tables and put each subform on one page of the tab object.

[adwest]

My advice would be to use the tab Pages object. Bind the main form to the first property table an put the PropertyID Control on the form (You can hide it). Then create a subform for all four tables and put each subform on one page of the tab object.

I'll give this a try. Thank you!