Combo box that depends on other combo box

[kl2]

Hi,
I am working on a cost table that has two fields categoryID and
categoryDetailID. Both fields are linked to a separate look up table.

The selection of CategoryDetailID depends on which categoryID is first
selected.
for example, under "car" category there would be "car plate number" as the
category's sub item, for category "supplier" there would be "supplier name"
as the category's sub item.

When creating new records for the cost table field, catgoryID has to be
chosen first, then categoryDetailID which it's rowsource depends on
categoryID.

How should I set the two combo box up? and how should I handle edit record?

Thanks

Andrew

 

[Allen Browne]

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

 

[kl2 via AccessMonster.com]

it works!! thanks!! such a relief! this problem's bugging me for days!!
thanks!!

So far it works for single record view forms, I am also trying to have it
work on data sheet view forms, would it be possible for the requery to take
place for only one specific record?

Thank you so much for taking your time to help Allen.

Andrew

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

Hi,
I am working on a cost table that has two fields categoryID and

[quoted text clipped - 13 lines]

How should I set the two combo box up? and how should I handle edit
record?

 

[Allen Browne]

Access does not assign multiple memory spaces for the RowSource of a combo
in a continuous form/datasheet. You cannot requery a combo so it is
restricted in one row of a continuous form, but contains all the records on
the other rows.

If the bound column of the combo is visible, that presents no problem,
because Access can show the known value. If the bound column is zero-width
and you restrict the combo, it has no idea what value to display on the rows
that don't have a match, so the combo goes blank on those rows.

You cannot change that behavior for the combo. You can design around it if
you use the text as the primary key of the combo's lookup table (rather than
some hidden number.) This solution is most suitble for simple things like
categories where a single Text field can easily serve as the natural key to
the table and an artificial key is superfluous.

There are other more involved workarounds, such as basing the continuous
form on a query that contains both its table and the lookup table, so you
have the text for the field you want to display. You can then place a text
box on top of the combo in the form, so it shows on every record, and in the
text box's GotFocus event, SetFocus to the combo that it beneath it. It
turns out the the combo jumps in front of the text box only on the selected
row, so the text box still shows the value from the query on all other rows,
even when you restrict the combo's RowSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

it works!! thanks!! such a relief! this problem's bugging me for days!!
thanks!!

So far it works for single record view forms, I am also trying to have it
work on data sheet view forms, would it be possible for the requery to
take
place for only one specific record?

Thank you so much for taking your time to help Allen.

Andrew

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

Hi,
I am working on a cost table that has two fields categoryID and

[quoted text clipped - 13 lines]

How should I set the two combo box up? and how should I handle edit
record?