instant update combo list based on option group

deetron

New Member
Joined
Dec 17, 2004
Messages
43
I have a mutually exclusive group of dates (month, year, quater) which I select with an option (radio button). Right below is a combo box (i.e. jan/feb/mar, etc. for month option; 2003/2004/2005, etc. for year option...etc) which gives a list of dates based on selected option. I need this box to update instantaneously when I select a new option. I use Row Source Type = Table/Query control to build an expression for selection of combo list. The Row Source field is as follows:

SELECT DISTINCT Switch([grpDates]=1,[Month],[grpDates]=2,[Qtr],[grpDates]=3,[Year]) AS Dates FROM qryDates;

The problem I'm seeing is it appropriately choses the combo list based on selected option only 1st time. It doesn't update when I re-select I new option. I am forced to close the form and re-open it again.

How do we tell Row Source to update instantly (I though Switch function is an instant function - I also tried using IIF(), which produced same result).

What am I doing wrong???
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
This is a standard issue with combo and list boxes, as well as subforms. First time you select or acitivate, fine. Next time you need to requery the combo.

To get to the module behind the form, do this:
Right-click the option group, select Properties, click the Events tab, and double-click the blank line next to After Update. You'll see [Event Procedure].
Click the Builder (...) button and paste this code in the blank line:
[MyComboName].Requery (change to suit the name of your control)

You'll need to do this for each Option Group.

Denis
 

Forum statistics

Threads
1,147,622
Messages
5,742,205
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top