[AccessD] Cascading Combos

Actually the ()'s and ""'s are mine for the e-mail (Don't know why I put
both) . The actual SQL Statement was a direct copy and paste from the
SQL view if the QBE grid.

Bryan Carbonnell
bryan_carbonnell at cbc.ca

>>> mikedorism at adelphia.net 18-Mar-04 4:23:45 PM >>>
<snip>
Now, as soon as I try to change the rowsource of cboReason to filter
based
on cboReasonType ("SELECT ReasonID, ReasonType_FK, ReasonDescription,
ReasonDiscontinued FROM tblReason WHERE
(tblReason.ReasonType_FK=[Forms]![sfmEditIncidentDetails]![cboReasonType]);"
)
I end up with blanks in both cboReason and cboReasonType. 

What am I missing?
<snip>

You are missing the fact that you referred to the form inside the
quotes
instead of outside.  Try...

"SELECT ReasonID, ReasonType_FK, ReasonDescription, ReasonDiscontinued
FROM
tblReason WHERE(tblReason.ReasonType_FK=" &
[Forms]![sfmEditIncidentDetails]![cboReasonType])

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com 



-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bryan
Carbonnell
Sent: Thursday, March 18, 2004 3:10 PM
To: accessd at databaseadvisors.com 
Subject: [AccessD] Cascading Combos


I'm stuck and need some help. I think I've been thinking and working on
this
problem too long. I can't see what I'm missing. <sigh>

I have 3 tables:

tblIncident
-----------
IncidentID (AutoNum PK)
IncidentReasonID_FK (Long)
....

tblReason
----------
ReasonID  (AutoNum PK)
ReasonDescription (Text)
ReasonTypeID_FK (Long)
ReasonDiscontinued (Boolean)

tblReasonType
---------------
ReasonTypeID (AutoNum PK)
ReasonTypeDescription (Text)
ReasonTypeDiscontinued (Boolean)

tblIncident is a 1-M with tblReason on ReasonID_FK=ReasonID tblReason
is a
M-1 with tblReasonType one ReasonTypeID_FK=ReasonTypeID

What I am trying to do is create cascading combos on an edit only form.
No
additions or deletions. Just editing The form is based on a query that
pulls
the data from tblIncident

cboReasonType is the ReasonType and is not bound
bboReason is Limited to only those Reasons that have a ReasonTypeID_FK
=
cboReasonType and is bound to IncidentReasonID_FK

So Far So good.

Now I have cboReasonType's rowsource to be a query that includes all of
the
ReasonTypes (SELECT ReasonTypeID, ReasonTypeDescription,
ReasonTypeDiscontinued FROM tblReasonType;)

cboReason's rowsource is a query with the SQL "SELECT ReasonID,
ReasonType_FK, ReasonDescription, ReasonDiscontinued FROM tblReason;"

In the Current Event of the forms I have:

'Set the Reason Type
cboReasonType = cboReason.Column(1)

So as I navigate to the various records, I get the value of the
RecordType
Combo correct.

Now, as soon as I try to change the rowsource of cboReason to filter
based
on cboReasonType ("SELECT ReasonID, ReasonType_FK, ReasonDescription,
ReasonDiscontinued FROM tblReason WHERE
(tblReason.ReasonType_FK=[Forms]![sfmEditIncidentDetails]![cboReasonType]);"
)
I end up with blanks in both cboReason and cboReasonType. 

What am I missing? 

Is it that cboReason's RowSource is dependant on cboReasonType's
value,
which is in turn based on cboReason's value?

If that is the problem, and now that I have typed this all out, I think
it
is, how the heck do I make it do what I want??