Stewart wrote:
Hi all! My (relatively small) database holds data on staff members and
the projects (services) that they are assigned to.
In my form frmStaff, I have a list of staff members - it is a
continuous form. Each staff member is linked to a Service through a
many-to-many relationship, using a junction table called
jctStaffServices. I would like to place a Combo Box in frmStaff where
you can 'filter' staff by the Service (i.e. ServiceName) that they are
assigned to.
What would be the easiest way to achieve this?
I'm presuming that I need to enter code in the AfterUpdate of the Combo
Box?
My tables are structured as follows:
tblStaff:
- Staff_ID (Autonumber)
- EmployeeNo (Primary Key)
- Surname
- FirstName
- etc...
tblServices:
- Service_ID (Autonumber)
- ServiceCode (Primary Key)
- ServiceName
- Address
- etc...
jctStaffServices:
- EmployeeNo
- ServiceNo
My Combo Box is called SelectCombo, and the Row Source is currently:
SELECT DISTINCTROW tblServices.ServiceName
FROM (tblStaff INNER JOIN tblServices ON tblStaff.ServiceCode =
tblServices.ServiceCode) INNER JOIN jctStaffServices ON
(tblStaff.EmployeeNo = jctStaffServices.EmployeeNo) AND
(tblServices.ServiceCode = jctStaffServices.ServiceCode)
ORDER BY tblServices.ServiceName;
Hope all this makes sense. Any help would be greatly appreciated.
PS I'm a newbie so apologies if my description of the problem isn't the
best!
I would create a combo box with a rowsource of jctStaffServices and
tblServices that would contain the ServiceID and ServiceName. I would
make it Distinct (or use a totals query) and order by ServiceName. I
might even have a row that has All Services to you can remove the
filter. Ex:
Select EmpID, EmpName From Employees Order By EmpName
UNION
Select 0, "*All Employees" As EmpName From Employees
The above selects all records from the table and floats all employees to
the top of the list. You can do something similar with yours.
In your forms Recordsource you would create a filter. Let's say the
form is called MainForm and the combo is called Combo0. Set it's
default value to 0. Find the column for the ServiceID and in the
criteria row enter
Forms!MainForm!Combo0
This will filter all records in the form to the value in the combo box.
Now, create another column in the recordsource query and enter
Forms!MainForm!Combo0
In the criteria row BELOW the first criteria enter
0
You have now created an OR condition. Only select records that have a
serviceID equal to the combo box OR if combo is 0 then select all records.
In the AfterUpdate event of the combo you could then enter
Me.Requery
This will refilter your records.
You could forget putting in the filter in the recordsource query and in
the afterupdate event put in something like
If Me.Combo0 > 0 then
Me.Filter = "ServiceID = " & Me.Combo0
Else
Me.Filter = ""
Endif
Me.FilterOn = True
to create the filter
