Filtered forms and combo boxes

[Guest]

Sorry if this is a bit long, I'm just trying to be thorough! Here goes...
I'm fairly new to access and have to make a database other people in the
office can use to keep track of contact they have with various companies. The
database revolves around the list of companies (all in table 1) with whom we
work. For each company there may be as many as five different contacts. All
contacts are in table 2, linked to table 1 via the company they work for (ie:
the key).

The database centres around a form that lists each company and the
associated contacts and address are shown as one scrolls through the form
from one company to the next.

What staff members would like to be able to do is add a new correspondence
with a certain company in a user friendly way (ie: they are not access
users). Ideally, I'd like them to be able to select a company via the main
form, then click a button that opens a new for entitled "Enter new
correspondence". This form would then be used to populate a table of
correspondences (table 3, say...) but would be filtered such that the user
can select which contact at the company they spoke to. eg: a drop down list
where he/she could only chose a contact at that company, to avoid erroneous
data input.

So basically I'd like the "Enter new correspondence" form to be filtered
each time its opened to reflect what record was being viewed in the previous
form listing all the companies. Secondly, the user should be able to select
who they spoke to at that company via a drop-down menu of sorts. Is this
possible?

Any help would be much appreciated!

 

[kingston via AccessMonster.com]

It sounds like you're on the right track with Company & Contact. Hopefully,
you implemented this via a subform:

Table1 - Company (w/ key CompanyID)
Table2 - Contact (w/ FK CompanyID) (and PK ContactID - see below)

Simply create a form for Table1, and while in design mode, drop in Table2.
Access will create a subform for you and you can specify that the two forms
should be linked via CompanyID. Actually, this is just so that you get an
idea of how subforms work; you'll drop in the form you create below, not
Table2.

Now, duplicate the exact same process with Contact & Correspondence:

Table2 - Contact (w/ key ContactID)
Table3 - Correspondence (w/ FK ContactID)

Create a form for Table2 and drop in Table3 where the two are linked by
ContactID. Now use this form as the subform for part 1 (Company & Contact).

You'll end up with a 3-tiered form and no coding. HTH