Multiple combo box management

[Ken Cobler]

I have read multiple emails relating to having one combo box determine the
items in another combo box - - and that part seems to work.

However, whenever I open the form, although the "child" cbx is filled out,
the "parent" box is blank. I believe this has to do with the form's table
only keeping (retaining) the "child" ID. I need to have the form show both
combo box selections, though, but not sure of the code to do so.

Here's more background:

*tblProperties*
PropertiesID
SubmarketID
(plus many other fields)

*tblMarkets* (the parent)
MarketID
MarketName

*tblSubmarkets* (the child)
SubmarketID
SubmarketName
MarketID (my foreign key)

I have related tblSubmarkets to tblMarkets via the MarketID in both.

Ideally, my main table (tblProperties) only needs to retain the unique
SubmarketID for it to know both the Market and Submarkets for each record.

My combobox cbxMarket as an AfterUpdate procedure which makes the
cbxSubmaret only show those Submarkets which belong to the selected Market.

Please tell me what additional info you need to know.

Thanks for your help.

 

[Marshall Barton]

I have read multiple emails relating to having one combo box determine the
items in another combo box - - and that part seems to work.

However, whenever I open the form, although the "child" cbx is filled out,
the "parent" box is blank. I believe this has to do with the form's table
only keeping (retaining) the "child" ID. I need to have the form show both
combo box selections, though, but not sure of the code to do so.

Here's more background:

*tblProperties*
PropertiesID
SubmarketID
(plus many other fields)

*tblMarkets* (the parent)
MarketID
MarketName

*tblSubmarkets* (the child)
SubmarketID
SubmarketName
MarketID (my foreign key)

I have related tblSubmarkets to tblMarkets via the MarketID in both.

Ideally, my main table (tblProperties) only needs to retain the unique
SubmarketID for it to know both the Market and Submarkets for each record.

My combobox cbxMarket as an AfterUpdate procedure which makes the
cbxSubmaret only show those Submarkets which belong to the selected Market.

Please tell me what additional info you need to know.

Your table structure is technically correct although many
people would bail out on this issue by adding the submarket
field to tblProperties.

Since the market combo box is unbound, nothing happens until
you store a calulated value in it or a user selects an item
from the list. You can calculate the value very simply by
including the MarketID field in the submarket combo box's
RowSource query (set its ColumnWidth to 0 so it doesn't show
in the list). Then use code in the form's Current event to
copy the market id value to the market combo box.

Me.cboMarket = Me.cboSubMarket.Column(2)

 

[Ken Cobler]

Thanks, Marshall, I'll give that a try.

I will also try the "punt" by just adding the MarketID along with the
SubmarketID to the tblProperties so the combo box is bound.

Thanks for your help.