Limiting fields

  • Thread starter Thread starter ladybug via AccessMonster.com
  • Start date Start date
L

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?
 
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
 
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!

Jeff said:
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
Click to expand...
[quoted text clipped - 24 lines]
Can someone help me make the resp party entries not appear if they are not
active?
Click to expand...
Click to expand...
 
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

ladybug via AccessMonster.com said:
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!

Jeff said:
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
Click to expand...
[quoted text clipped - 24 lines]
Can someone help me make the resp party entries not appear if they are
not
active?
Click to expand...
Click to expand...
Click to expand...
 
Only certain employees are in each department. That is why they are limited.

Jeff said:
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.
Click to expand...
[quoted text clipped - 26 lines]
Click to expand...
 
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


ladybug via AccessMonster.com said:
Only certain employees are in each department. That is why they are
limited.

Jeff said:
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.
Click to expand...
[quoted text clipped - 26 lines]
not
active?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top