Combo Boxes and Using AfterUpdate

[Ken Cobler]

I've been having "fun" with using two combo boxes, the first one of which
determines the content of the second one. I then have the recommended
AfterUpdate event for the first one which then directs the SELECT...WHERE row
source is for the second.

This all works just fine, as long as I am editing Markets and Submarkets (my
two combo boxes) at the same time on a record...

Unfortunately the AfterUpdate only works if the first comboBox experiences
an Update. But when a user is later just scrolling through records, and
attempts to re-edit the second combobox, the SELECT...WHERE filter does not
work and ALL choices are shown. This is because there was no Update of the
first combobox.

I have experimented in vain with putting similar code in some other events,
but I am very new to event procedures and I get lots of weird results that
don't work.

For Further info, this is my AfterUpdate code for the cbxMarket combobox.
(I am controlling the fields Markets and Submarkets.)

Me!cbxSubmarket.RowSource = _
"SELECT SubmarketID, Submarket, MarketID " & _
"FROM tblSubmarkets " & _
"WHERE [MarketID] = " & Me!cbxMarket & " " & _
"ORDER BY tblSubmarkets.Submarket"


Thanks for your help!

 

[Marshall Barton]

I've been having "fun" with using two combo boxes, the first one of which
determines the content of the second one. I then have the recommended
AfterUpdate event for the first one which then directs the SELECT...WHERE row
source is for the second.

This all works just fine, as long as I am editing Markets and Submarkets (my
two combo boxes) at the same time on a record...

Unfortunately the AfterUpdate only works if the first comboBox experiences
an Update. But when a user is later just scrolling through records, and
attempts to re-edit the second combobox, the SELECT...WHERE filter does not
work and ALL choices are shown. This is because there was no Update of the
first combobox.

I have experimented in vain with putting similar code in some other events,
but I am very new to event procedures and I get lots of weird results that
don't work.

For Further info, this is my AfterUpdate code for the cbxMarket combobox.
(I am controlling the fields Markets and Submarkets.)

Me!cbxSubmarket.RowSource = _
"SELECT SubmarketID, Submarket, MarketID " & _
"FROM tblSubmarkets " & _
"WHERE [MarketID] = " & Me!cbxMarket & " " & _
"ORDER BY tblSubmarkets.Submarket"

I thought I explained that before. Assuming the Market
combo box's BoundColumn is the MarketID field, just use the
form's Current event to set its value:

Me.cbxMarket = Me.cbxSubmarket.Column(2)

ANother thought with the dependent combo scenario is that
the market combo box's AfterUpdate event shuld probably set
the Submarket combo box's value to Null ao it doen't retain
a value that's unrelated to the newly selected market.

 

[Ken Cobler]

Hi, Marshall.

I took your original advice by adding both a MarketID and SubmarketID field
to tblBuildings, so no longer is the cbxMarket unbound.

I tried setting the form's current event so that:
Me.cbxMarket = Me.cbxSubmarket.Column(2)

- Actually, the tblSubmarket uses its column #3 as the MarketID reference.
Is that what I should refer to? I tried this statement with either Column(3)
or (2) and neither worked...

- I don't understand the last part of your reply about setting the
cbxSubmarket to Null. (I'm new at this.)

Any elaboration would be appreciated. Thank you.

I've been having "fun" with using two combo boxes, the first one of which
determines the content of the second one. I then have the recommended
AfterUpdate event for the first one which then directs the SELECT...WHERE row
source is for the second.

This all works just fine, as long as I am editing Markets and Submarkets (my
two combo boxes) at the same time on a record...

Unfortunately the AfterUpdate only works if the first comboBox experiences
an Update. But when a user is later just scrolling through records, and
attempts to re-edit the second combobox, the SELECT...WHERE filter does not
work and ALL choices are shown. This is because there was no Update of the
first combobox.

I have experimented in vain with putting similar code in some other events,
but I am very new to event procedures and I get lots of weird results that
don't work.

For Further info, this is my AfterUpdate code for the cbxMarket combobox.
(I am controlling the fields Markets and Submarkets.)

Me!cbxSubmarket.RowSource = _
"SELECT SubmarketID, Submarket, MarketID " & _
"FROM tblSubmarkets " & _
"WHERE [MarketID] = " & Me!cbxMarket & " " & _
"ORDER BY tblSubmarkets.Submarket"

I thought I explained that before. Assuming the Market
combo box's BoundColumn is the MarketID field, just use the
form's Current event to set its value:

Me.cbxMarket = Me.cbxSubmarket.Column(2)

ANother thought with the dependent combo scenario is that
the market combo box's AfterUpdate event shuld probably set
the Submarket combo box's value to Null ao it doen't retain
a value that's unrelated to the newly selected market.

 

[Marshall Barton]

If the combo box is bound, you do not need that code. The
combo box's value is set from the record source.

Maybe I'm not understanding the question. Is the question
how to get the SubMarket combo box's list to sync with the
Market combo box's bound value as you navigate from record
to record? If that's what you're looking for, then the
Current even only needs:
Me.cbxSubmarket.Requery

As for the Column number issue, the Column index is zero
based so Column(2) refers to the third field in the combo
box's RowSource table/query.

You should probably clear (set to Null) the submarket value
when a different market is selected. If you don't do this,
you will have a submarket that is not in the list for the
selected market. To see this effect, select a market and
then select a submarket. After that's done, change your
mind and select a different market. Now look at the
submarket, since you have not yet selected a new submarket,
shouldn't the submarket combo box display a blank (Null)
value?
--
Marsh
MVP [MS Access]

I took your original advice by adding both a MarketID and SubmarketID field
to tblBuildings, so no longer is the cbxMarket unbound.

I tried setting the form's current event so that:
Me.cbxMarket = Me.cbxSubmarket.Column(2)

- Actually, the tblSubmarket uses its column #3 as the MarketID reference.
Is that what I should refer to? I tried this statement with either Column(3)
or (2) and neither worked...

- I don't understand the last part of your reply about setting the
cbxSubmarket to Null. (I'm new at this.)

Any elaboration would be appreciated. Thank you.

I've been having "fun" with using two combo boxes, the first one of which
determines the content of the second one. I then have the recommended
AfterUpdate event for the first one which then directs the SELECT...WHERE row
source is for the second.

This all works just fine, as long as I am editing Markets and Submarkets (my
two combo boxes) at the same time on a record...

Unfortunately the AfterUpdate only works if the first comboBox experiences
an Update. But when a user is later just scrolling through records, and
attempts to re-edit the second combobox, the SELECT...WHERE filter does not
work and ALL choices are shown. This is because there was no Update of the
first combobox.

I have experimented in vain with putting similar code in some other events,
but I am very new to event procedures and I get lots of weird results that
don't work.

For Further info, this is my AfterUpdate code for the cbxMarket combobox.
(I am controlling the fields Markets and Submarkets.)

Me!cbxSubmarket.RowSource = _
"SELECT SubmarketID, Submarket, MarketID " & _
"FROM tblSubmarkets " & _
"WHERE [MarketID] = " & Me!cbxMarket & " " & _
"ORDER BY tblSubmarkets.Submarket"

I thought I explained that before. Assuming the Market
combo box's BoundColumn is the MarketID field, just use the
form's Current event to set its value:

Me.cbxMarket = Me.cbxSubmarket.Column(2)

ANother thought with the dependent combo scenario is that
the market combo box's AfterUpdate event shuld probably set
the Submarket combo box's value to Null ao it doen't retain
a value that's unrelated to the newly selected market.