Combo Box cascading query

Most likely I am making this harder than it should be!!!! You Guru's may
laugh at this one, but I just can't get it.

The problem - The combo boxes only work where there is a unique
FRDF_CATEGORY.CHILD_REF=FRDF_CATEGORY.CATEGORY in the FRDF_CATEGORY table!
I thought I could get the Parent_ID/Child_ID concept to work but I couldn't
so I went with looking up the the CATEGORY and comparing it to the
CHILD_REF. I hope this makes since! But ideally, I should be working on
Parent_ID/Child_ID concept so that the combo box selection can be grown to
CATEGORY_C and CATEGORY_D. My query/lookup knowledge is holding me back
here. I can post my SQL lookups on each of the three combo boxes that I have
working but I think they need to change!

SQL2000 back end and Access2003 front end database not ADP yet. Below is
the table structu

FRDF_CONTACT (table) mainform
ROW_ID - autonumber
C_DEFINITION - text
UNIT_ID - text
COMPLETION_DATE - sql select from a view based on UNIT_ID
RECORD_CREATE_DATE - date/time

FRDF_DETAILS (table) subform
ROW_ID - autonumber
D_DEFINITION - text
CONTACT_UNIT_ID - text (mainform subform link)
DETAIL_COMMENTS - memo
ORIGINATOR - sql select from a view
bla bla bla - other unrelated fields
CATEGORY_A - text
CATEGORY_B - text
CATEGORY_C - text
CATEGORY_D - text
CATEGORY_E - text
FRDF_COST - currency
bla bla bla - other unrelated fields

FRDF_CATEGORY (table)
ROW_ID - autonumber hidden on form, primary Key
PARENT_ID - text
CHILD_ID - text, child records of the RECORD_ID
CHILD_REF - current reference to category
CATEGORY - text, description of the incident saved in
FRDF_DETAILS.CATEGORY_(A-E)

ACTION_TYPES (table)
DISPOSITION_TYPES (table)
REPORTS (table)
SYSUSERS (view)
EMPLOYEE (view)
PARTS (view)
VENDORS (view)
REPRESENTATIVES (view)

Thanks for any insights you can give me!
Rohn

"Rohn" wrote

Most likely I am making this harder than it should be!!!!

Yes, you are, but it is us for whom you are making this harder than it needs
to be -- make it a little easier for us to help you. Instead of saying what
problem you are having (that is, what doesn't work), start by telling us
what you have, and what you are trying to accomplish with your cascading
combo boxes. It's possible that by carefully reading, and re-reading what
you posted that you have in tables, and "only work where..." that we could
make a guess at these, but frankly, if you want to get an intelligible
answer in a newsgroup, you really need to ask an intelligible question. Few
who post here have time or energy to dig deep, study carefully, to try to
figure out what you are trying to accomplish (and still, perhaps, not get
that right).

For some other good suggestions on effective use of newsgroups, see the FAQ
at http://www.mvps.org/access/netiquette.htm.

Larry Linson
Microsoft Office Access MVP

P.S. You state "not ADP yet", as though it were a given that an Access
client to SQL Server should be ADP. Are you aware that the Access product
team no longer recommends ADP as the method of choice for an Access client
connecting to an SQL Server back end database? ADPs still work, you can
still create them, but are not represented as "the only way" nor necessarily
"the better way" to work with SQL Server from Access. And, in my experience,
were not necessarily "the better way" even when so many were touting them as
such... they were neither as difficult to work with as some thought, nor
nearly so efficient as others did, but they did require you take a somewhat
different view, and learn the somewhat different approach to input/output
programming that ADO used (IMNSHO, "different" not "better").

Larry

Our Customer Service team wants to track incoming field response problems.
So, I have a simple form/subform. On the SubForm there are three cascading
combo boxes that I want to drill down to the root cause of the customer
problem.

SYSTEM ComboBox identifies ten systems where the problem may have originated
LOCATION ComboBox allows a defined list based on the SYSTEM selected in the
first ComboBox
FAILURE_MODE ComboBox is the final drill down from LOCATION to FAILURE_MODE

I have some conflicting data that I am getting from my CATEGORY table which
suggests the structure is weird, this is where I need help. This is what the
saved data looks like:

SYSTEM - LOCATION - FAILURE
Body - Doors - Bent
Body - Hinge - Broken
Body - Shelf - Cracked
Paint - Crane - Thin
Paint - Doors - Run
Paint - Bumper - Rust

I get both types of doors in my dropdown list in the LOCATION dropdown,
which makes more list items show up in the FAILURE_MODE then what should be
there. I'm sure there is a solution, I'm just not seeing it. I posed the
table structure previously.

Thank you for the help,
Rohn