Selecting within subforms

[Colin Foster]

Hello Group
I've built a database that records projects, the companies that are
tendering for that project and the contacts within each company. These are
displayed in a series of forms & sub forms as follows...
FrmProjects
subform frmTenderers
subform frmContacts

A company may have 3 contacts that I deal with, however it may be that Tom
deals with Project #1, Dick with Project #2 and Harry with Project #3.
What I would like to be able to do is, initially show all contacts for the
job and then select (via a check box) the contact (or contacts) that are
relevant to this project (maybe even filtering out the others). However,
when I then start a new project and link this company to it, I need to see
all contacts so that I can select the ones that are relevant to Project #2,
without then affecting those that are relevant to Project #1.

Any suggestions would be gratefully received.

Regards

Colin Foster

 

[Larry Daugherty]

Hi Colin,

Correct schema is required to make something like this work effortlessly
(well, almost).

Your entities are: Companies/Tenderers, Contacts and Projects. Each
requires a table. tblProject and tblTerderer exist as independent entities.
tblContact is a child of tblTendeerer. Therefore, the primary key of this
companies record in tblTenderer appears as a long integer foreign key in
tblContact. Thus when your subform frmTenderers brings a particular
tenderer into focus, only that Tenderer's Contacts should be visible for
selection for this project.

Just a suggestion: if you are going to limit the number of contacts to 4 or
so, I'd just use comboboxes to select and show them rather than go through
the hassle of creating and fitting another sobform.

You'll need Data entry forms for entering the Companies, Contacts and
Projects. Be kind to your users (even if you are the only user). Make the
same kind of trade off decision about Contact subform or comboboxes.

HTH

 

[Colin Foster]

Hi Larry,
I like the combo box idea (& saves much recoding of database), 'though it
might be an issue as more than 4 options are required, however, it's set a
train of thought going. Thanks for the help - but sorry for the delayin
responding (been away from the office)
Regards
Colin Foster