filtering or limiting combo/list box from another combo/list box

[jmk]

Hi,

I am trying to filter or limit the contents of one combo/list box based on
the selection from another combo/list box. Both are on the same form and use
different rowsource types and row sources.

The combo/list box that lists staff names and their level is the one I want
to use to filter the other combo/list box, this one has a list of procedures
that are specific to a staff's level.

I am not an expert at programming and have tried some of the solutions that
I have found in other posts, but haven't been able to get them to work.

Any additional help would be greatly appreciated.

 

[fredg]

Hi,

I am trying to filter or limit the contents of one combo/list box based on
the selection from another combo/list box. Both are on the same form and use
different rowsource types and row sources.

The combo/list box that lists staff names and their level is the one I want
to use to filter the other combo/list box, this one has a list of procedures
that are specific to a staff's level.

I am not an expert at programming and have tried some of the solutions that
I have found in other posts, but haven't been able to get them to work.

Any additional help would be greatly appreciated.

Here is some generic code to do what you want.
Change the Control, Field and Table names below, as needed.

Leave the rowsource of the 2nd combo box blank.
Code the AfterUpdate event of the 1st combo box to fill the rowsource
of the 2nd.
Something like this:
Combo2.Rowsource = "Select Instructors.InstructorID,
Instructors.InstructorName from Instructors Where Instructors.ClassID
= " & Me!ComboName & ";"

The above assumes ClassID is a Number datatype and the combo box bound
column is Number also.

 

[Graeme Richardson]

Hi, I don't have your tables so I'll use my own to illustrate the
methodology

Two combo boxes:
The first, cboFkSection, determines what will be displayed in the second
cboFkStaff
I.e. you choose a section and you see the staff in that section

Data source for cboFkSection (2 columns, column 1 bound) is:
SELECT tlkpSection.uidSection, tlkpSection.Section
FROM tlkpSection
ORDER BY tlkpSection.Section;

Data source for cboFkStaff (3 columns, column 1 bound) is:
SELECT tblStaff.uidStaff, tblStaff.Staff, tblStaff.fkSection
FROM tblStaff
WHERE (((tblStaff.fkSection)=[cboFkSection])) ORDER BY tblStaff.Staff;

Note the WHERE clause in the cboFkStaff statement uses the value from the
cboFkSection combobox to restrict records shown.

Now, MS Access doesn't bind the SQL statement in the cboFkStaff combobox
hence you need to tell it to update each time you change cboFkSection. To do
that, capture the cboFkSection AfterUpdate event (Properties > Events >
After Update...) and place the statement

cboFkStaff.Requery

Done.

Note: for completeness, it's a good idea to rename the controls on your
forms so that they are different to the ControlSource property - i.e. don't
use the default name placed when you drag bound controls to the form. (e.g.
a TextBox with ControlSource "StaffName" would be become "txtStaffName",
ComboBox bound to "fkSection" would become "cboFkSection", etc.)

HTH, Graeme.

 

[jmk]

Thanks for the quick response. It makes sense, but I'm not sure quite how to
apply it. This is how the form and the combo boxes are currently set up.

The form is based on a table called Service Transactions

The first combo box, named: Service By Drop Down, with a control source of
Service By, Row source Type is Table/Query and the Row source is SELECT
DISTINCTROW Q_PersonnelA.Name, Q_PersonnelA.[Staff Level] FROM Q_PersonnelA;

The second combo box is named: Select List with a control source of Service
Procedure, Row source type is Table/query and the row source is SELECT
[Service Procs].Code, [Service Procs].[Staff Level 1], [Service
Procs].Description FROM [Service Procs];

The table Service Procs that the second combo box is pulling info from has 5
different fields (field 1 = RSA, field 2 = MHP, field 3= PhD, etc..) to
reflect the different staff levels for each procedure. (Each procedure can
either allow only one type of staff level or multiple staff levels) The
Personnel table that the query combo 1 uses just has the one field to
identify the correct level for each staff (JMK = MHP)

I'm thinking I may need to change some properties of the table(s) or maybe
not. Would you be able to apply this information to the information you
posted, so I can fully understand how it should work together.

Thank you for your help

Hi, I don't have your tables so I'll use my own to illustrate the
methodology

Two combo boxes:
The first, cboFkSection, determines what will be displayed in the second
cboFkStaff
I.e. you choose a section and you see the staff in that section

Data source for cboFkSection (2 columns, column 1 bound) is:
SELECT tlkpSection.uidSection, tlkpSection.Section
FROM tlkpSection
ORDER BY tlkpSection.Section;

Data source for cboFkStaff (3 columns, column 1 bound) is:
SELECT tblStaff.uidStaff, tblStaff.Staff, tblStaff.fkSection
FROM tblStaff
WHERE (((tblStaff.fkSection)=[cboFkSection])) ORDER BY tblStaff.Staff;

Note the WHERE clause in the cboFkStaff statement uses the value from the
cboFkSection combobox to restrict records shown.

Now, MS Access doesn't bind the SQL statement in the cboFkStaff combobox
hence you need to tell it to update each time you change cboFkSection. To do
that, capture the cboFkSection AfterUpdate event (Properties > Events >
After Update...) and place the statement

cboFkStaff.Requery

Done.

Note: for completeness, it's a good idea to rename the controls on your
forms so that they are different to the ControlSource property - i.e. don't
use the default name placed when you drag bound controls to the form. (e.g.
a TextBox with ControlSource "StaffName" would be become "txtStaffName",
ComboBox bound to "fkSection" would become "cboFkSection", etc.)

HTH, Graeme.

 

[Graeme Richardson]

Hi,it might help to post the table structure of the two tables and how they
are linked.
e.g.
Q_PersonnelA
Name
Staff Level

Service Procs
Code
Staff Level 1
Description

Reading your scenario it looks like you need to normalise the Service Procs
table. Unless by the explanation "...different fields (field 1 = RSA, field
2 = MHP, field 3= PhD, etc..) ..." you mean "... different records (record 1
= RSA,..."

Which control do you populate first, [Service By Drop Down] or [Select List]

Graeme.

 

[jmk]

Graeme,

I realize I neglected to state that Q_PersonnelA is a query. The table it
is based on is called Personnel.

I am at a disadvantage of tyring to make changes to database that has been
modified by various means over the years by my agency and some of the
programming behind is convoluted and doesn't make sense sometimes. I will
try to be as brief as I can in outlining the way the combo boxes were set up
and being used.

The form itself is for data entering services that our staff provides to our
clients. The data entry was pretty simple, client name, date, start time,
end time, off site or onsite, service procedure (combo/list box) and staff
name. When a service procedure was selected it listed the appropriate
billing code in a separate field and then listed the description in the drop
down. When the staff name was selected it listed the staff's ID in a
separate field and the staff name in the drop down. The two boxes didn't
have specific relationship with each other, not like I need them to be now.

Due to changes in Federal and State regulations (HIPPA) there were a lot of
changes in the way the services are documented and entered. So now all of
the service procedures are based on our staff's credential level (RSA, MHP,
QMHP, MA, Ph.D, etc) this was simple enough to add to the personnel table, I
added a field called Staff Level and for each staff indicated their level.
For the services procedure table, it was a little more complicated because
there are services that only an RSA can provide, but an MHP can perform any
service that an RSA provides plus what is specific to their level, so on an
so forth for a QMHP, MA, PH.D,etc. So I added 5 additional fields to the
Service proc table to accomodate this.

So the drop down that has the staff names has two columns, Name and Staff
Level with Name being the bound column -I switched the staff ID with the
Staff Level. The drop down that has the service procedures currently has 3
columns, Code, Staff level 1, description (I was trying to just use the Staff
Level 1 instead of all five just to see if it worked) - Staff level 1 is
populated by RSA, Level 2 MHP, Level 3 QMHP, Level 4 Masters, Level 5 Other.
The bound column is 1, Code. This field is the billing code that appears in
a different field when a service procedure is selected.

I was trying to use and change what already existed, but I'm thinking that
there may be some other changes to either both of the underlying tables or
the form itself. I was hoping it would be just a simple thing to do, to
filter one from the other.

I do appreciate the time and help you are giving me and I hope this
information helps.

Hi,it might help to post the table structure of the two tables and how they
are linked.
e.g.
Q_PersonnelA
Name
Staff Level

Service Procs
Code
Staff Level 1
Description

Reading your scenario it looks like you need to normalise the Service Procs
table. Unless by the explanation "...different fields (field 1 = RSA, field
2 = MHP, field 3= PhD, etc..) ..." you mean "... different records (record 1
= RSA,..."

Which control do you populate first, [Service By Drop Down] or [Select List]

Graeme.

 

[Graeme Richardson]

You posted too much information.

Keep it simple
Q1) What is the structure of the tables/queries in each of the controls (a
list box and combobox)?
Q2) What is the common field between each table/query in Q1?
Q3) What is the name of the listbox control?
Q4) What is the name of the combobox control?
Q5) Which control do you populate first, the listbox or combobox?

Let's establish this and then worry about the next bit.

 

[jmk]

Okay.

Q1) Both are combo boxes.
Combo box: Service by drop down
Table: Personnel, 46 fields, those that are relevant to this:
Name and Staff Level; Both are text fields

Combo box: Select List
Table: Service Procs, 28 fields, those that are relevant to this:
Code, Staff Level 1, Staff Level 2, Staff level 3, Staff level 4, Staff
level 5,
Description; All are text fields

Q2) Technically there is not a common field, the closest is the staff level
and staff level 1-5 fields.

Q3/Q4) The control source for each combo box:
Service by drop down: Service by
Select list: Service procedure

Q5) Currently it doesn't matter which one is selected. I need the Service
by drop down to be poplulated first.

Thanks

 

[PC Datasheet]

Your problem stems from an incorrect design of your tables! If you can
change that, this problem will go away. In fact you don't even need two
comboboxes. Tables should be:

TblStaffLevel
StaffLevelID
StaffLevel

TblPersonnel
PersonnelID
PersonName
StaffLevelID
etc

Since TblPersonnel has 46 fields, there's a 99% chance that there are other
problems too with the design of your tables!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[Graeme Richardson]

Hi, I think that this is it. Set your controls up like:

[Service by]
set ColumnCount property to 2
set RowSource to:
SELECT Name, [Staff Level] FROM Personnel, ORDER BY Name

[Service procedure]
Set ColumnCount property to 6 (or 1, your call)
set RowSource to:
SELECT Code, [Staff Level 1], [Staff Level 2], [Staff Level 3], [Staff Level
4], [Staff Level 5]
FROM [Service Procs]
ORDER BY Code

In AfterUpdate event of [Service By] place statement:
[Service procedure].RowSource ="SELECT Code, [Staff Level 1], [Staff Level
2], [Staff Level 3], [Staff Level 4], [Staff Level 5] " & _
"FROM [Service Procs] " & _
"WHERE ([Staff Level 1]=" & [Service by].Column(1) & ") OR " & _
([Staff Level 2]=" & [Service by].Column(1) & ") OR " & _
([Staff Level 3]=" & [Service by].Column(1) & ") OR " & _
([Staff Level 4]=" & [Service by].Column(1) & ") OR " & _
([Staff Level 5]=" & [Service by].Column(1) & ") OR " & _
ORDER BY Code

Note, the Column property of a combobox is 0 based. [Service by].Column(1)
will return the value in the second column.

Should do it, Graeme.

 

[jmk]

Thanks for info Graeme.

I started with Service By, but I am getting a "Syntax error in FROM clause"
in the RowSource and nothing shows up in the drop down.

I clicked on .... and made sure that the table was there and both fields,
name and staff level had been selected. The system automatically put the
following in the Rowsource:
SELECT Personnel.Name, Personnel.[Staff Level] FROM Personnel;

I added the ORDER BY to the end of it, but it still gave the same syntax
error.

Also, the bound column is set to 1.

 

[jmk]

I got through the first two, but have some questions on this statement.

1. I am assuming this isn't a single line statement. Where do I return for
each line? and do place the _ after each &.

2. For each staff Level do I need to change the column number accordingly?

Thanks

JMK

In AfterUpdate event of [Service By] place statement:

 

[Graeme Richardson]

Sorry, the statement is incomplete: should read:

Me![Service procedure].RowSource = "SELECT Code, [Staff Level 1], " & _
"[Staff Level 2], [Staff Level 3], [Staff Level 4], [Staff Level 5] " &
_
"FROM [Service Procs] " & _
"WHERE ([Staff Level 1]=" & Me![Service by].Column(1) & ") OR " & _
"([Staff Level 2]=" & Me![Service by].Column(1) & ") OR " & _
"([Staff Level 3]=" & Me![Service by].Column(1) & ") OR " & _
"([Staff Level 4]=" & Me![Service by].Column(1) & ") OR " & _
"([Staff Level 5]=" & Me![Service by].Column(1) & ") " & _
"ORDER BY Code"

The statement continues to a new line after each underscore (_) character.
You should be able to copy and paste the above statement.

Graeme.

 

[jmk]

Hi Graeme,

I cut and pasted the code and tried it. I am getting the following error:

Runtime error:
Object doesn't support this property or method.

Also, there was another field called description, that I had listed along
with the relevant fields for the combo box Select List. I don't see it
listed as part of this code, where should I insert it?

Thank you.

 

[Graeme Richardson]

I cut and pasted the code and tried it. I am getting the following error:

Runtime error:
Object doesn't support this property or method.

I'll ned a bit more information,
Post the procedure and show which line produces error.

Also, there was another field called description, that I had listed along
with the relevant fields for the combo box Select List. I don't see it
listed as part of this code, where should I insert it?

Place it as second field in the query,
e.g.
SELECT Code, Description, [Staff Level 1], ...

change the number of columns to 7 (or 2) so that you see the extra field.

 

[jmk]

Here is the procedure. The whole thing, starting at Me! through ORDER BY was
highlighted yellow for the error.

Private Sub Service_By_Drop_Down_AfterUpdate()
Me![Service procedure].RowSource = "SELECT Code,Description, [Staff Level
1], " & _
"[Staff Level 2], [Staff Level 3], [Staff Level 4], [Staff Level 5] " & _
"FROM [Service Procs] " & _
"WHERE ([Staff Level 1]=" & Me![Service by].Column(2) & ") OR " & _
"([Staff Level 2]=" & Me![Service by].Column(2) & ") OR " & _
"([Staff Level 3]=" & Me![Service by].Column(2) & ") OR " & _
"([Staff Level 4]=" & Me![Service by].Column(2) & ") OR " & _
"([Staff Level 5]=" & Me![Service by].Column(2) & ") " & _
"ORDER BY Code"
End Sub

I cut and pasted the code and tried it. I am getting the following error:

Runtime error:
Object doesn't support this property or method.

I'll ned a bit more information,
Post the procedure and show which line produces error.

Also, there was another field called description, that I had listed along
with the relevant fields for the combo box Select List. I don't see it
listed as part of this code, where should I insert it?

Place it as second field in the query,
e.g.
SELECT Code, Description, [Staff Level 1], ...

change the number of columns to 7 (or 2) so that you see the extra field.

 

[Graeme Richardson]

Bah!
From the event procedure I can see the the control is called "Service By
Drop Down"!

Hence statement becomes:
Me![Service procedure].RowSource = "SELECT Code,Description, [Staff Level
1], " & _
"[Staff Level 2], [Staff Level 3], [Staff Level 4], [Staff Level 5] " &
_
"FROM [Service Procs] " & _
"WHERE ([Staff Level 1]=" & Me![Service By Drop Down].Column(2) & ") OR
" & _
"([Staff Level 2]=" & Me![Service By Drop Down].Column(2) & ") OR " & _
"([Staff Level 3]=" & Me![Service By Drop Down].Column(2) & ") OR " & _
"([Staff Level 4]=" & Me![Service By Drop Down].Column(2) & ") OR " & _
"([Staff Level 5]=" & Me![Service By Drop Down].Column(2) & ") " & _
"ORDER BY Code"

If code breaks on this statement again then, in immediate window (<Ctrl> +
<G>), run the statements

?Me![Service procedure].Name
?Me![Service By Drop Down].Column(2)

and say what happens.

Cheers, Graeme.

 

[Graeme Richardson]

Sorry, the naming error was my mistake.

 

[jmk]

Well,....
Sorry to say,

I am getting the same error, with everything highlighted. Additionally,
there is an arrow pointing to the ORDER BY statement.

 

[Graeme Richardson]

If code breaks on this statement again then, in immediate window (<Ctrl> +
<G>), run the statements

?Me![Service procedure].Name
?Me![Service By Drop Down].Column(2)

and say what happens.

Cheers, Graeme.