Flexible Option Group via VBA

[SusanV]

Good morning all,

I need to create an Option Group to filter records on a continuous form, but
the option group will have a different number of choices and different
labels depending on which set of records the user is currently viewing. For
instance, if he is viewing AFS records, I need 3 choices, Sirius, Saturn and
Spica - or perhaps, in some cases, only the Sirius and Saturn. Similarly, if
he is viewing ATF records, I need 4 choices, Apache, Navajo, Catawba and
Sioux, but perhaps only 2 or 3 of these depending on the current record.

I already have this data (which ones would need to be included for each
record) stored in a table which is part of the recordsource for the form,
but can't seem to find any info on creating the option group on the fly to
include only the valid choices.

The source table is structured like so (fields are RepNum/Text, Vess/Text
and Included/YesNo):

ATF-001 Apache True
ATF-001 Catawba False
ATF-001 Navajo True
ATF-001 Sioux True

ATF-002 Apache True
ATF-002 Catawba True
ATF-002 Navajo True
ATF-002 Sioux True

AFS-001 Sirius True
AFS-001 Spica False
AFS-001 Saturn True

AFS-001 Sirius True
AFS-001 Spica True
AFS-001 Saturn True

So for the above records, I would want to show ONLY the number of options
where Included = TRUE, labeled as the data in the field Vess. I've already
got the form filtered by RepNum, so now it's a matter of grabbing the data
from the other 2 fields for the Option Group filter.

This *seems* like it ought to be simple enough, but i can't seem to work it
out. Hopefully this is clear enough that someone can point me in the right
direction!

TIA,

Susan

 

[Dirk Goldgar]

Good morning all,

I need to create an Option Group to filter records on a continuous
form, but the option group will have a different number of choices
and different labels depending on which set of records the user is
currently viewing. For instance, if he is viewing AFS records, I need
3 choices, Sirius, Saturn and Spica - or perhaps, in some cases, only
the Sirius and Saturn. Similarly, if he is viewing ATF records, I
need 4 choices, Apache, Navajo, Catawba and Sioux, but perhaps only 2
or 3 of these depending on the current record.

I already have this data (which ones would need to be included for
each record) stored in a table which is part of the recordsource for
the form, but can't seem to find any info on creating the option
group on the fly to include only the valid choices.

The source table is structured like so (fields are RepNum/Text,
Vess/Text and Included/YesNo):

ATF-001 Apache True
ATF-001 Catawba False
ATF-001 Navajo True
ATF-001 Sioux True

ATF-002 Apache True
ATF-002 Catawba True
ATF-002 Navajo True
ATF-002 Sioux True

AFS-001 Sirius True
AFS-001 Spica False
AFS-001 Saturn True

AFS-001 Sirius True
AFS-001 Spica True
AFS-001 Saturn True

So for the above records, I would want to show ONLY the number of
options where Included = TRUE, labeled as the data in the field Vess.
I've already got the form filtered by RepNum, so now it's a matter of
grabbing the data from the other 2 fields for the Option Group filter.

This *seems* like it ought to be simple enough, but i can't seem to
work it out. Hopefully this is clear enough that someone can point me
in the right direction!

Hi, Susan. It seems to me that the easiest way to do this would be to
have 4 different option groups, and just show or hide the appropriate
one as needed. The option groups would be otherwise static, so the code
would be simple.

If that won't do, then you would still do better not to try creating
controls on the fly. Instead, you could create one option group at
design time, with the maximum number of options you think you might
need. Then at run time, use the information in the table to show or
hide options, change label captions, and maybe move things around and
resize them. The code for that would be much more complicated, but
fairly straightforward.

A third approach would be to use a combo box instead of an option group.
That way you'd just have to modify the rowsource of the combo box to get
the options you need into the list.

 

[SusanV]

Hi Dirk,

Using static groups per record type isn't an option, as new record groups
will be defined in the future and I don't want to have to go back in and
create new ones as we move on. Also I would still need to remove some
options on the fly, as not all records in a group pertain to all options.

After playing with this for the past couple of hours I've pretty much
decided to create a static group of 15 buttons ( the max number) and show
or hide and label them via code, using the onClick of each button to filter
the list by that option.

Thanks for the advice - as usual you're right on the money

SusanV