Filtering forms data with a drop down box

[Paul S]

Hi

I have to related tables - Employee and activity - one Employee can have
several activities so they are linked with a EmployeeId in
the Activity table.
The activities are shown in a list on a form which also has a drop down
containing all the employees (fields are Name, Id).
Based on the selected employee I want only his activities listed. Also when
a new activity is added the activity should be connected to the
selected Employee by inserting the id for the current selected Employee.

How can I solved this - I have been looking for an example wihtout any luck.

Thanks Paul S

 

[Allen Browne]

Most likely this is a many to many relation, i.e.:
- one employee can have many activities, and
- one activity can apply to many employees.

You will therefore need 3 tables, like this:
- Emp table (one record for each person), with EmpID primary key
- Activity table (one record for each activity), with ActivityID primary key
- EmpActivity table, with fields:
o EmpID relates to Emp.EmpID
o ActivityID relates to Activity.ActivityID
So, if a person has 4 activities, their EmpID will appear in 4 rows of this
table.

To interface it, create a main form bound to the Emp table, with a subform
bound to the EmpActivity table. Show the subform in Continuous Form view (so
you can see multiple records), and use a combo box for the ActivityID. You
can now add as many rows as you need to the subform, just by choosing an
activity in the combo box in each row.

BTW, in the Emp table, it would be better to use 2 fields for Surname and
FirstName. It will make it easier and more efficient to seach and match
people. Also, nearly everything in Access (such as forms) has a Name
property, so Access will get confused if you use the field name Name (i.e.
sometimes it thinks you mean the name of your form instead of the contents
of the field named Name.)

 

[Paul S]

Hi Allen
You're right about 3 tables - I do have 3 tables I just wanted the scenario
to be as simple as possible. I thought it could be done with some sort of
filtering instead of a subform. I tried with an eventhandler for the Change
event on the combo box containing a DoCmd Applyfilter
"EmployeeId=me.cbEmployee.value"
but that doesn't work

I that a wrong way to go or is the only possibility the subform - any
examples ?

Thanks
Paul S

 

[Allen Browne]

You can filter a form so that it is limited to one employee, e.g.:
Dim strWhere as string
strWhere = "EmployeeID = 99"
Me.Filter = strWhere
Me.FilterOn = True

Or you can write the strWhere clause so that it filters to one activity if
you wish.

How you actually apply that will be up to you, but that's how it's done.

 

[Paul S]

Hi
That was exactly was I was looking for and it works.
Thanks a lot, Allen

Paul S