Linked forms in CRM database

[fordrules01]

Hi,

2 questions to ask.

1) I have a CRM database that tracks all correspondance to a specific
contact through "ContactID". These contacts are then linked through joining
tables to both the centres and councils they work at. I have 3 main forms set
up for "Contacts", "Councils", "Centres". Each of these currently has a
number of subforms which show the relationships between the 3 entities and
the specific one i'm interested in is the "Correspondance_Subform".
Correspondance is only linked to ContactID however i want to display all the
correspondance for a council even if its through more than one contact.

I'll talk in terms of 2 forms here just to make it easier and only deal with
the "Council" and "Contacts" form. I have the "Council correspondance
subform" linked to the "CouncilContacts_Subform". The "Council
correspondance subform" currently shows only the correspondance for which
ever record is active in the "CouncilContacts_Subform". Is there a simple way
to get it to display all correspondance for all contacts listed in the
"CouncilContacts_Subform"?

2) Dealing with the same 2 forms. I want the user to be able to add
correspondance with a council straight from the council subform. Where i run
into problems if the contact they are making it with is not currently in the
ComboBox that lists all our contacts. I have set up a command button that
opens the a new contact form but i want it to automatically link it to the
council through the "ContactsAtCouncil_Subform" so the user does not have to
type this in.

All help greatly appreciated.

Cheers

 

[MarkK]

Question 1) not sure I follow totally, but it seems that you could join table Councils with table Contacts on ContactID, and in the same query join table Correspondence with table Contact on ContactID and thereby return all correspondence for the council. Then leverage the CouncilID as the LinkChild and LinkMaster Fields for the subform.
Question 2) mostly lost you, but if Correspondence is only linked to Council through Contact, it seems that Contact will always need to be explicitly specified by the user when adding Correspondence. Not clear how ComboBox fits in. If Contact does not exist, user must create it and then select it, no?
Cheers,

 

[fordrules01]

Thanks for the reply lagbolt,

1) Contacts can be linked to councils, companies or centres. If i follow your approach and join all into the one query i run into problems being able to edit from within the seperate forms as its linking 4 forms together.

2) Sorry this question was unclear. I understand they need to add a new contact and fill in the details. This question was really about populating the "CouncilID" into the new contact information so the user does not need to type this in. The contact form used to enter this information uses tabs and the council details appear on another tab so i just want this to be automatically populated so theres no chance of the user missing it.

Thanks