I have an Order Form where I have to input one or more Inspection contacts -- In time, these Inspection contacts might come back once or twice. In about 9000 records, I would say about 10% would show up more that once but no more than 3 times.

Each contact has one role
Each contact can have one or more phone numbers
Each contact can have one email

My tables are normalized so I have this structure :

tblOrders
OrderID (PK)

tblOrderContacts
OrderContactID (PK)
OrderID (FK)
PeopleRoleID (FK)

tblPeopleRoles
PeopleRoleID (PK)
PeopleID (FK)
RoleID (FK)

tblRoles
RoleID (PK)
Role
tblPeople
PeopleID (PK)
FirstName
LastName

tblPhoneNumbers
PhoneNumberID (PK)
PhoneNumberTypeID (FK)
PeopleID (FK)
PhoneNumber

tblPhoneNumberTypes
PhoneNumberTypeID (PK)
PhoneNumberType

tblEmails
EmailID (PK)
EmailTypeID (FK)
Email
PeopleID (FK)

tblEmailTypes
EmailTypeID (PK)
EmailType

I have attached a picture of the form I'm working on

Question is : How can I best make it works so that it looks like what's in the red box... Do I have to create a bunch of subforms on my main Order form or should I go with unbound controls and populate fields in the tables with VBA ?
Or since there is so little repeating Inspection contacts, is this a case where I should bend normalization a bit ?

I'm opened to all suggestions !

Thanks in advance

Chris

I would use subforms, and put them on a different tab.

Thanks for the response.

I thought about that but my client wants to be able to have a whole view of the order on the main form. How would you "nest" all the subforms referring to the contacts ?

Chris

Depends on your relations. Problem is you can't have a continuous form subform on a continuous form subform.

You're right!!! Forgot about this one !!! How would you do it ?

I'm not real clear on the relations or the levels. But only the bottom level can be a continuous form. The parent levels have to be regular forms.

Its hard to tell what fields you're trying to return as the form is not in design view. But, why can't you make a query that returns the data you need and then make a subform from that query? The subform can also have a Form Header that can be used for grouping if need be.

FiftyFour,

I only designed some controls so that readers of this post can see the end result I wish to accomplish with this form.

This part of the form with the red rectangle is the part I am having trouble with. It wil fill
Contact's Role and Name -- linked with tblPeopleRole
Phone numbers (with combo box to select the phone type) -- linked with tblPhoneNumbers
Email if any -- linked with tblEmails

Problem is, since these controls are linked with different tables and as ScottGem pointed out I "can't have a continuous form subform on a continuous form subform" which would have been the ideal way to do it... since one
Orders can have one or more inspection contacts and contact may have one or more phone numbers.

Any thoughts ?

Chris

Sorry, now I understand...Scott suggested another tab which you said won't work...how about a pop up form with a subform then? Open it as a dialog, set some public variables to whatever data the user selects? I'm out of ideas after that.

It's an idea that I could probably use if I can't find another way around.

Thanks

Chris

Chris,

Is this a Data entry form or just for display? You can use a query as the basis for a subform to display the contacts, joiningg the tables to display all the info in continuous form. You can have an Add contact button, that opens a data entry form where you can enter new contacts.

That's certainly another alternative. From where I stand right now, I'd like to rule out the possibility to either enter or view the data in the main form without having to resort to tabs, buttons or pop-up forms (It's a lot faster and easier for my client if everything is in the main form). I'm looking at 2 options to do this.

1- The easiest way is to bend normalization rules and create a single table with InspectionContacts info since there is so few redundant inspection contacts. This table would the look something like :
InspectionContactID (PK)
ContactRoleID (FK)
PeopleID (FK)
OrderID (FK)
EmailID (FK)
And limit to 3 phone numbers

2- The other option, a bit more complex, would be to make the form show only 3 contacts (as in my jpg), have unbound combo box or text controls that would populate (when viewing) or write (when editing) tables with VBA -- mimicking somewhat Outlook contact forms.

But since I am surely not the only one struggling with that kind of problem, I thought I might ask for other options I didn't see...

Chris