I have a table with 2 columns, Col1 = Departments, Col2 = Staff Names.
In my form I want to select in a ComboBoc the Department, in ComboBox2
I want to show only the names that work in that department based my
selection on Combo1. Any help please
Let's say your form is named frmMyForm (this will only work on a Form, not in
a Table: see
http://www.mvps.org/access/lookupfields.htm for a critique of
Table Lookup Fields), and the department combo is named cboDepartment. Its
RowSource would be
SELECT DISTINCT Departments From Yourtable ORDER BY Departments;
Your second combo could have a recordsource such as
SELECT [Staff Names] FROM Yourtable WHERE Departments =
Forms!frmMyForm!cboDepartment ORDER BY [Staff Names];
You will need to Requery the second combo in the AfterUpdate event of the
first one, this can be done with a macro or VBA code.
Note that you will probably at some point want to normalize your tables a bit
more; I'd expect to have tables like
Departments
DepartmentID <primary key>
DepartmentName
<possibly other department-specific fields>
Staff
StaffID <primary key, a unique employee number>
LastName
FirstName
MiddleName
Title
DepartmentID
<other biographical or personal information>
--
John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also
http://www.utteraccess.com
