Filtered Lookup list in Combo Box

J

Jim Shaw

I need to filter the value list in a combobox.
I have tables as follows:
tblA:
----
RecID Autonumber (Primary key)
ReasonCode Text (Uses combo box lookup into tblB)

tblB:
----
RecID Autonumber (Primary key)
ActionType Integer
ReasonCode Text
ReasonDescr Text

These tables are related on the ReasonCode Field.

I have a form bound to tblA
frmA:
----
cboReason (bound to tblA.ReasonCode,
4columns - widths = 0;0;0;3
bound column = 3)
optActionType Integer (unbound option box)

Users of frmA will not have to memorize the reason codes
due to the fact that I only display the Reason
Description. This is good and I need to keep this feature.

However, I need to restrict the reason codes available to
the user to that with a matching ActionType. How do I
apply this restriction to my design?

Thanks
 
G

Greg Kraushaar

Since you have a table to back it up, I assume you are not really
using a Value List ofr the row source, but are using a query instead.

Change the RowSource of the Combo
to
Select ReasonCode, ReasonDesc where ActionType = n

If the Action code changes on the form, change the the rowsource when
the action code changes and requery the combo. You will need to watch
out for a valid ReasonCode becoming invalid when the actionType
changes though

Also, ReasonCode looks like a candidate for a Primary key to me. Is
there a reason why its not?
 
G

Guest

I'm too embarrased to tell you what I was doing in the
RowSource! Your approach is much better! Don't know why
I didn't think of it.

The requery of the combo box proved interesting. Had to
do that in the form's OnCurrent event AND in the
fraActionType.OnClick event. (I forgot to mention that
fraActionType is an option frame bound to a
field "ActionType" in tblA...Sorry).

The need to requery in the OnClick event was obvious.

Initally, I tried the requery in the OnUpdate event, but
that event is not fired as you navigate through the
recordset, causing fraActionType to display differing
values found in the recordset. I wonder why that is so??
fraActionType IS being updated by the values in the
recordset. I can see the check boxes changing as I
navigate thru the records??!!

As for the ReasonCode being a Primary Key. Some reasons
are valid for more than one type code to duplicate are
possible. I can make the type code and the reason code
into a compound Primary key.

Thanks for you help. Made my life a lot easier.
-Jim
 
Top