Question about tables and combo boxes

[marf]

I have a table that contains information about projects going on in my company.

The ProjectsTbl links to a ResourcesTbl by ProjNum, it is a one-to-many
relationship.

ProjectsTbl
ProjNum
ProjDate
ProjStatus
ProjNotes
.... etc

ResourcesTbl - Basically it contains a list of the employees working on this
project and their depts

ProjNum - foreign key
ResourceID - autonumber
ResourceDeptName
ResourceLastName
ResourceFirstName
ResourcePhoneNum

I also have an EmployeeTbl that contains the names of all employees.
Basically what I would like to do is to create a form where I could enter the
resources assigned to a project. But I need the following functionality. I
want a drop down for the ResourceDept field where I can choose the dept. I
want the next box to show me only the employees that work for the dept that I
selected in the first box and not all 200 or so and then I need an "add
button" or something so I can assign another resource to the same project if
needed

 

[scubadiver]

For the combo boxes look up "cascading combos". There are plenty of threads.

 

[Klatuu]

This technique is commonly known as Cascading Combos. It is not that
complex. It just involves filtering the second combos row source based on
the value of the first combo. For example, let's say you have an EmpID that
is the primary key of tblEmployee, EmpName, and EmpDept fields tblEmployee.
You construct the employee combo to filter on EmpDept:

SELECT EmpID, EmpName FROM tblEmployee WHERE EmpDept = Me.cboDept;

Then in the After Update event of cboDept, you requery cboEmployee and it
will then show only employees for the selected department.

Me.cboEmployee.Requery

 

[Ken Sheridan]

A rider to Steve's reply:

Scenario 1: You should probably not include a DepartmentID column in the
ProjectResources table. It introduces redundancy in that for every time an
employee appears in the table it will tell you what department they are in.
The department is implied by the employee, so you don't need it. More
importantly it leaves the table at risk of inconsistent data being entered.
In the jargon DepartmentID is said to be 'transitively functionally
dependent' on the key of ProjectResources, which means the table is not
properly normalized.

Scenario 2: I said 'probably not' above as the inclusion of a DepartmentID
column in ProjectResources would be legitimate if the employee might change
departments, but you would want the department of which they were a member at
the time of the project to remain recorded as a resource of the project. In
this case the department is not implied by the employee, at least not
time-independently so, so there is no transitive functional dependency; it is
functionally dependent on the key of the table, which is consequently
properly normalized (to Third Normal Form at any rate). However, if this
scenario is the one to be modelled rather than one where the department is
always implied by the employee, then you would not be able to use correlated
combo boxes while at the same time using 'surrogate' EmployeeID and
DepartmentID keys. It would be necessary for you to use 'natural' keys, i.e.
the names of the department and employee rather than an arbitrary unique
number. With the latter, if an employee changes department, their name would
no longer appear in the employee combo box on the form because their
EmployeeID value would no longer be in the subset of EmployeeID values for
the selected DepartmentID. The underlying value of the combo box and would
still be correct, you just would not see the employee's name in it. While
using the department name as a key is fine, because the names will be unique
within the organization, the same is not true of employee names, which can be
duplicated. Catch 22!

With scenario 1 you can still use correlated combo boxes in the way you
wish. In single form view you would use an unbound department combo box and
a bound employee combo box; in continuous form view you'd use hybrid
department and employee controls made up of a text box superimposed on a
combo box in each case, in the case of department both unbound, in the case
of employee a bound combo box and an unbound text box. You'll find a demo of
this at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


This uses the local administrative units of county, district and parish in
my area, but the principle is the same. The demo includes an alternative
approach of using a single combo box with a multi-column list, and unbound
text boxes referencing its columns. If you do download it you'll see that
I'm not too keen on the use of hybrid correlated combo/text boxes in
continuous forms, favouring the latter approach, principally because of
performance issues, but with a 2-level hierarchy like yours the objection is
less.

Ken Sheridan
Stafford, England