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

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

Post by am1r » Wed, 26 Jan 2005 06:01:02


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
 
 
 

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

Post by Graeme Ric » Wed, 26 Jan 2005 06:59:43

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.

 
 
 

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

Post by am1r » Wed, 26 Jan 2005 07:47:04

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.
 
 
 

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

Post by Graeme Ric » Wed, 26 Jan 2005 08:28:32

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.
 
 
 

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

Post by am1r » Thu, 27 Jan 2005 02:43:11

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
 
 
 

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

Post by PC Datashe » Thu, 27 Jan 2005 03:44:18

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!





level
Service

(a
 
 
 

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

Post by Graeme Ric » Thu, 27 Jan 2005 04:04:16

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.
 
 
 

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

Post by am1r » Thu, 27 Jan 2005 06:25:10

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.
 
 
 

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

Post by am1r » Thu, 27 Jan 2005 06:53:02


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:
 
 
 

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

Post by Graeme Ric » Thu, 27 Jan 2005 09:27:58

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.
 
 
 

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

Post by am1r » Fri, 28 Jan 2005 03:25:04

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.
 
 
 

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

Post by Graeme Ric » Fri, 28 Jan 2005 04:33:04

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

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


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.
 
 
 

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

Post by am1r » Fri, 28 Jan 2005 05:23:05

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
 
 
 

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

Post by Graeme Ric » Fri, 28 Jan 2005 05:44:29

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.
 
 
 

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

Post by Graeme Ric » Fri, 28 Jan 2005 05:45:49

Sorry, the naming error was my mistake.