By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,718 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

Combo Box to Filter Records in a Continuous Form

P: n/a
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!

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article explains how to dynamically change the RecordSource of the main
form to an INNER JOIN, so it contains only records that have a match in the
subform. It is also possible to use a subquery in the Filter of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stewart" <st*****@v6media.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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!

Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

P: n/a
Cheers! Thanks to both your help - I've now resolved the problem.

The fix was as follows:

Private Sub SelectCombo_AfterUpdate()
Dim strSQL As String
If IsNull(Me.SelectCombo) Then
Me.RecordSource = "tblStaff"
Else
strSQL = "SELECT DISTINCTROW tblStaff.* FROM tblStaff INNER JOIN
jctStaffServices ON tblStaff.EmployeeNo = jctStaffServices.EmployeeNo
WHERE
(((jctStaffServices.ServiceCode)=[Forms]![newfrmStaff]![SelectCombo]));"
Me.RecordSource = strSQL
End If
End Sub

The row source is set to tblServices. The Bound Column is the
ServiceCode.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.