Limiting fields

[ladybug via AccessMonster.com]

I have a form with two fields: Department and ResponsibleParty. This form is
bound to a table called tbl_Department_Employee
There is another text field in the table called Active. This field says
either "yes" or "no." I want only the active responsible parties to appear.
I have the three fields in the SQL statement of the Row Source of Responsible
Party on the form. In the acive criteria I have "yes." When I run the sql
statement only the entries set to "yes" appear. However, when I go back to
the form they are all still appearing.
I do have this code in the After Update Event of the Department field in the
form:

On Error Resume Next
ResponsibleParty.RowSource = "Select tbl_Department_Employee.
ResponsibleParty " & _
"FROM tbl_Department_Employee " & _
"WHERE tbl_Department_Employee.Department = '" & Department.Value
& "' " & _
"ORDER BY tbl_Department_Employee.ResponsibleParty;"

Can someone help me make the resp party entries not appear if they are not
active?

 

[Jeff Boyce]

How is the list being generated? If you are using a combobox to list the
"Active" Employees, what is the SQL statement of THAT control, not the form
itself...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ladybug via AccessMonster.com]

The Row Source for Department is SELECT DISTINCT tbl_Department_Employee.
Department FROM tbl_Department_Employee ORDER BY tbl_Department_Employee.
Department;

The Row Source for Responsible Party is SELECT tbl_Department_Employee.
ResponsibleParty, tbl_Department_Employee.Department, tbl_Department_Employee.
Active FROM tbl_Department_Employee WHERE (((tbl_Department_Employee.Active)
="yes")) ORDER BY tbl_Department_Employee.ResponsibleParty;


Thank you for your help!

How is the list being generated? If you are using a combobox to list the
"Active" Employees, what is the SQL statement of THAT control, not the form
itself...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with two fields: Department and ResponsibleParty. This form
is

[quoted text clipped - 24 lines]

Can someone help me make the resp party entries not appear if they are not
active?

 

[Jeff Boyce]

Why are you limiting the row source for [Department] to only those already
entered in the [tblDepartment_Employee]? I would hope that you'd have a
tlkpDepartment that lists all valid (and maybe historical) departments.
That way your row source isn't depending on having a value already entered
in the Department_Employee table.

Ditto for "responsible party" ... don't you want to have any (or ALL)
employees available to be a "responsible party"? If so, look to your
tblEmployee, not those limited few who have already been entered in the
tblDepartment_Employee.

Regards

Jeff Boyce
Microsoft Office/Access MVP

The Row Source for Department is SELECT DISTINCT tbl_Department_Employee.
Department FROM tbl_Department_Employee ORDER BY tbl_Department_Employee.
Department;

The Row Source for Responsible Party is SELECT tbl_Department_Employee.
ResponsibleParty, tbl_Department_Employee.Department,
tbl_Department_Employee.
Active FROM tbl_Department_Employee WHERE
(((tbl_Department_Employee.Active)
="yes")) ORDER BY tbl_Department_Employee.ResponsibleParty;


Thank you for your help!

How is the list being generated? If you are using a combobox to list the
"Active" Employees, what is the SQL statement of THAT control, not the
form
itself...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with two fields: Department and ResponsibleParty. This
form
is

[quoted text clipped - 24 lines]

Can someone help me make the resp party entries not appear if they are
not
active?

 

[ladybug via AccessMonster.com]

Only certain employees are in each department. That is why they are limited.

Why are you limiting the row source for [Department] to only those already
entered in the [tblDepartment_Employee]? I would hope that you'd have a
tlkpDepartment that lists all valid (and maybe historical) departments.
That way your row source isn't depending on having a value already entered
in the Department_Employee table.

Ditto for "responsible party" ... don't you want to have any (or ALL)
employees available to be a "responsible party"? If so, look to your
tblEmployee, not those limited few who have already been entered in the
tblDepartment_Employee.

Regards

Jeff Boyce
Microsoft Office/Access MVP

The Row Source for Department is SELECT DISTINCT tbl_Department_Employee.
Department FROM tbl_Department_Employee ORDER BY tbl_Department_Employee.

[quoted text clipped - 26 lines]

 

[Jeff Boyce]

I'm confused. I thought your Department_Employee table shows "What Is".
Does this mean you will NEVER have a new employee?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Only certain employees are in each department. That is why they are
limited.

Why are you limiting the row source for [Department] to only those already
entered in the [tblDepartment_Employee]? I would hope that you'd have a
tlkpDepartment that lists all valid (and maybe historical) departments.
That way your row source isn't depending on having a value already entered
in the Department_Employee table.

Ditto for "responsible party" ... don't you want to have any (or ALL)
employees available to be a "responsible party"? If so, look to your
tblEmployee, not those limited few who have already been entered in the
tblDepartment_Employee.

Regards

Jeff Boyce
Microsoft Office/Access MVP

The Row Source for Department is SELECT DISTINCT
tbl_Department_Employee.
Department FROM tbl_Department_Employee ORDER BY
tbl_Department_Employee.

[quoted text clipped - 26 lines]

not
active?