Multiple contacts at multiple clients

[Guest]

In our business transactions we have numerous corporate clients and I have
them all in a Clients table, with a combo on the Jobs form so that the
appropriate client company can be selected for each job.
At many of these clients there are multiple contact persons, and once we
have selected the client on a Job we want to use another combo to find just
the handful of contact persons at that particular firm. Along with other data
such as their direct dial number, mobile phone number and various other
details.
I have no problem setting up a Clients table and a Contacts table witht eh
necessray information. But how do I link them (assuming that is necesary) and
how do I get them to behave in the required manner on the form - the contacts
displayed in the combo are to be dependent upon the already selected client
company??
Looking fwd to your help!
Many thanks
CW

 

[Allen Browne]

CW, you have:
- one Client can have many Contacts.
- one Client can have many Jobs.

Which of these describes what you want to do?
a) Display all the contacts for the selected client in the job.
b) Select one of the client's contacts as the key person for that job.

If (a), you could just use a subform (probably read-only) to the job form.
It will show the contacts for the client, using the
LinkMasterFields/LinkChildFields. No code needed. Subform is blank until a
client is selected, and then displays all the matching contacts.

If (b), you want a 2nd combo on the Jobs form for selecting the contact
person. Use the AfterUpdate event of the ClientID combo to set the RowSource
of the ContactID combo. This article illustrates it:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm
You will also need to use the Current event of the form (so the combo's
RowSource gets updated as you change record), and the form's Undo event
(using the OldValue of the ClientID so as to set the RowSource is set back
to the right client.)

For the (b) case, if the combo's bound column is hidden, there can be a
display issue when you set its RowSource in Form_Current. The combo looks
blank, even though there are characters there.

 

[Guest]

Allen -
Yes, your opening summary is exactly right.
Option (b) will be the betterone, because we definitely need to have the
selected Contact's details shown on the Job form itself, rather than on a
subform. Presumably I can pull several of the Contact's attributes along with
his/her name (e.g. direct phone nbr, mobile nbr etc) and have those display
on the form too?

Anyway I will go to that article and study it and as always, am most
grateful for your help.
Thanks so much
CW

 

[Allen Browne]

Set up the RowSource of the combo so it has extra columns to show the
contact's phone number, mobile, etc. You can set the Column Widths property
so these extra columns are zero-width if you wish, and then show them in
text boxes on the form like this:
=[Combo1].Column(2)
etc.

Note that Column is zero-based, i.e. the first column is 0, the 2nd one is
1, and so on.