select case from combobox and option group

[Mark Kubicki]

I have an series of actions that need to be executed from differnt types of
controls on 2 different forms
one control is an option group, the second is a combobox (limit to list =
yes)

the commands had already been moved to a sub, SetPresetOptions(frm as
Access.form) <call SetPresetOptions(me) > , which is located in the same
module as the option group (and can be moved to a differnet module if
needed)

the code reads something like this:
Select Case optSetPresetOptions
case = 1
do something
case = 2
dosomething else
case = ....

How would I adjust the select code to also work with the combobox

many thanks in advance,
Mark

 

[Piet Linden]

I have an series of actions that need to be executed from differnt types of
controls on 2 different forms
one control is an option group, the second is a combobox (limit to list =
yes)

the commands had already been moved to a sub, SetPresetOptions(frm as
Access.form) <call SetPresetOptions(me) > , which is located in the same
module as the option group (and can be moved to a differnet module if
needed)

the code reads something like this:
    Select Case optSetPresetOptions
    case = 1
        do something
    case = 2
        dosomething else
    case = ....

How would I adjust the select code to also work with the combobox

many thanks in advance,
Mark

Basically the exact same, except you values could numeric or text,
depending on the value stored in the leftmost column in the combobox.

 

[BruceM via AccessMonster.com]

If the combo box has just a few possible selections you could nest an If
statement or a Select Case inside each Case of your existing statement. If
there are many possible combo box selections I don't think there is a generic
answer. It would depend on the type and value of the combo box selection,
and the action you would have performed. If you could post some specifics it
may help.

 

[Mark Kubicki]

I may have been a bit ambiguous:

- it is the same subroutine that would be called from both controls
- both controls are linked to the same [field], however,
~ at the option group the vaules returned (1, 2, 3... ), are used
to execute code that "updates" the value of the [field]
~ at the combobox, the row source is a table whose records are the
same as the values which the option group used in its after update event
- so, in one case I seem to need to check for values like 1, 2, 3... and in
the other values like: "1st draft", "preliminary", "final"...

Should I be executing the common code when the field value changes and not
after the control supdate (?)

-m.
------------------------------------------------------------------------------------------------------------

I have an series of actions that need to be executed from differnt types
of
controls on 2 different forms
one control is an option group, the second is a combobox (limit to list =
yes)

the commands had already been moved to a sub, SetPresetOptions(frm as
Access.form) <call SetPresetOptions(me) > , which is located in the same
module as the option group (and can be moved to a differnet module if
needed)

the code reads something like this:
Select Case optSetPresetOptions
case = 1
do something
case = 2
dosomething else
case = ....

How would I adjust the select code to also work with the combobox

many thanks in advance,
Mark

Basically the exact same, except you values could numeric or text,
depending on the value stored in the leftmost column in the combobox.

 

[Daryl S]

Mark -

You can nest Case statements if that is what you want to do:

Select Case optSetPresetOptions
case = 1
Select Case <ComboboxValue>
case = "value1"
do something
case = "value2"
dosomething else
case = ....

case = 2
Select Case <ComboboxValue>
case = "value1"
do something
case = "value2"
dosomething else
case = ....

case = ....

 

[Mark Kubicki]

- the option group (frame) is named: frmPresetOption, and has (6) choices
(radio buttons) which are the same as the records in the table
optPrintPresests, which is used as row source for the combo box.
- the table optPrintPresests has (6) records; the values of the 6 records
are the same as the lables for the option buttons (and are text)
- the slected value is stored in a table: tbeFixtureSchedulePrintOptions, in
its field [PresetTitle]

this is the beginning to the code behind the option group beforeupdate
event: (the predominance of it has been moved tothe subroutine...)

Private Sub frmPresetOption_BeforeUpdate(Cancel As Integer)
With Me
Set Db = CurrentDb()
Dim rst As DAO.Recordset
Set rst = Db.OpenRecordset("SELECT * FROM
[tbeFixtureSchedulePrintOptions]")
DoCmd.SetWarnings False

Select Case .frmPresetOption.Value
Case Is = 1 'first draft
[PresetTitle] = "First Draft"

'fixture identifier options
.frmManufacturersName = 1
With .chkInclCatalogNo
.Enabled = False
.Value = "no"
End With
With .chkInclAltMfrs
.Enabled = False
.Value = "no"
End With

'description options
.chkShortDescription.Value = "no"
.chkInclLocations = "yes"
.chkSeeSketch = "no"
...

 

[BruceM via AccessMonster.com]

The code needs to be in the After Update event. Before Update the selected
value has not been applied. Also, you do not show variable declaration for
db, which should be DAO.Database. However, I'm not sure why you are opening
a recordset at all. Is the form bound to a different recordset than the one
specified in the Set rst line of code?

Is the Option Group bound? I ask because from your description it sounds as
if the Option Group value is repeated in [PresetTitle], and somehow mirrors
the combo box too.

The real difficulty here is I cannot understand what you are trying to do.
Does the code you posted do part of what you need? If so, what is it failing
to do?

- the option group (frame) is named: frmPresetOption, and has (6) choices
(radio buttons) which are the same as the records in the table
optPrintPresests, which is used as row source for the combo box.
- the table optPrintPresests has (6) records; the values of the 6 records
are the same as the lables for the option buttons (and are text)
- the slected value is stored in a table: tbeFixtureSchedulePrintOptions, in
its field [PresetTitle]

this is the beginning to the code behind the option group beforeupdate
event: (the predominance of it has been moved tothe subroutine...)

Private Sub frmPresetOption_BeforeUpdate(Cancel As Integer)
With Me
Set Db = CurrentDb()
Dim rst As DAO.Recordset
Set rst = Db.OpenRecordset("SELECT * FROM
[tbeFixtureSchedulePrintOptions]")
DoCmd.SetWarnings False

Select Case .frmPresetOption.Value
Case Is = 1 'first draft
[PresetTitle] = "First Draft"

'fixture identifier options
.frmManufacturersName = 1
With .chkInclCatalogNo
.Enabled = False
.Value = "no"
End With
With .chkInclAltMfrs
.Enabled = False
.Value = "no"
End With

'description options
.chkShortDescription.Value = "no"
.chkInclLocations = "yes"
.chkSeeSketch = "no"
...

If the combo box has just a few possible selections you could nest an If
statement or a Select Case inside each Case of your existing statement.

[quoted text clipped - 29 lines]