[AccessD] Cascading Combos

Why not set the rowsource of the second combobox on enter?

cbo2ndBox_OnEnter()
me.cbo2ndBox.rowsource = "Select fields from table where foreignKey = " &
me.cbo1stBox.column(0)
End sub

then on enter or after update of cbo1, clear cbo2

me.cbo2ndBox = null

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Bryan
Carbonnell
Sent: Thursday, March 18, 2004 12: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??

Thanks,

Bryan Carbonnell
bryan_carbonnell at cbc.ca