On Sat, 3 Dec 2011 07:05:05 -0800 (PST), Paul <(E-Mail Removed)> wrote:
>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also
http://www.utteraccess.com
