option group - is this possible?

[PaulaCMT]

Hi there

Thanks to all who helped with my last query - i am having difficulties
again!!(surprise!)

I would like to put an option group on my subform with 3 options. Only one
of the three options can be selected. If the first option is selected then i
would like to calculate a fee and return it to 2 text boxes on the subform.
If the second option is selected then i would like it to return "0" to both
boxes and if the third option is selected then i would like to return £70 to
one of the boxes and 0 to the other.

I have my calculation in a query already but i am unsure of where to go from
here. Is this even possible?

Any comments would be gratefully received (or pointing and laughing will do
to!!!!)

Thanks

Paula

 

[Wayne Morgan]

Yes, this is possible. The part about only being able to select one of the
three items is no problem, that's exactly what an option group does for you.
The thing to remember about an option group is that it returns a number to
indicate which item was selected. It is then up to you to remember what item
1, 2, or 3 did so that you can react accordingly.

In this case, I would make the option group the bound control and the two
textboxes would be calculated controls. This will allow the option group
selection to be displayed when you go back to review the record. As you have
stated, the values of the textboxes are dependent on the selection of the
option group, so they will be calculated based on that selection. You can do
this calculation in the Control Source of the textboxes or you could create
two calculated fields in the form's Record Source query and bind the
textboxes to those calculated fields. The equation would be similar in both
cases.

Control Source for first textbox:
=IIf([OptionGroup1]=1, <your calculation here>, IIf([OptionGroup1]=2, 0,
70))

Checking for 3 isn't needed if you've set a Default Value for the option
group. If you haven't, then Null is a possibility and you'll need to check
for 3 so that Null doesn't return 70.

Control Source for second textbox:
=IIf([OptionGroup1]=1, <your calculation here>, IIf([OptionGroup1]=2, 0, 0))

 

[PaulaCMT]

Hi Wayne

Thank you for your reply - it makes perfect sense!! The only problem i have
is that the calculation that i need to do is a nested IIf. Isn't there a
restriction on the amount of IIF's that can be used ??

I did try the code with only some of the IIf's from the calculation and it
did work beautifully with the few records i have input to test it but
obviously i will need all the IIf's in.

Any ideas? Thanks

Paula

Yes, this is possible. The part about only being able to select one of the
three items is no problem, that's exactly what an option group does for you.
The thing to remember about an option group is that it returns a number to
indicate which item was selected. It is then up to you to remember what item
1, 2, or 3 did so that you can react accordingly.

In this case, I would make the option group the bound control and the two
textboxes would be calculated controls. This will allow the option group
selection to be displayed when you go back to review the record. As you have
stated, the values of the textboxes are dependent on the selection of the
option group, so they will be calculated based on that selection. You can do
this calculation in the Control Source of the textboxes or you could create
two calculated fields in the form's Record Source query and bind the
textboxes to those calculated fields. The equation would be similar in both
cases.

Control Source for first textbox:
=IIf([OptionGroup1]=1, <your calculation here>, IIf([OptionGroup1]=2, 0,
70))

Checking for 3 isn't needed if you've set a Default Value for the option
group. If you haven't, then Null is a possibility and you'll need to check
for 3 so that Null doesn't return 70.

Control Source for second textbox:
=IIf([OptionGroup1]=1, <your calculation here>, IIf([OptionGroup1]=2, 0, 0))

--
Wayne Morgan
MS Access MVP

Hi there

Thanks to all who helped with my last query - i am having difficulties
again!!(surprise!)

I would like to put an option group on my subform with 3 options. Only one
of the three options can be selected. If the first option is selected then
i
would like to calculate a fee and return it to 2 text boxes on the
subform.
If the second option is selected then i would like it to return "0" to
both
boxes and if the third option is selected then i would like to return £70
to
one of the boxes and 0 to the other.

I have my calculation in a query already but i am unsure of where to go
from
here. Is this even possible?

Any comments would be gratefully received (or pointing and laughing will
do
to!!!!)

Thanks

Paula

 

[John Vinson]

I did try the code with only some of the IIf's from the calculation and it
did work beautifully with the few records i have input to test it but
obviously i will need all the IIf's in.

Try the Choose() function instead:

Choose([OptionGroup], <result for 1>, <result for 2>, <result for 3>,
etc.)

John W. Vinson[MVP]

 

[PaulaCMT]

Thank you John - that worked perfectly - and thank you Wayne too! I was
struggling to know where to start!

This forum is absolutely brilliant - i have managed to work out most of my
problems by searching through past posts. Thanks again.

(I may be back!!)

Paula

I did try the code with only some of the IIf's from the calculation and it
did work beautifully with the few records i have input to test it but
obviously i will need all the IIf's in.

Try the Choose() function instead:

Choose([OptionGroup], <result for 1>, <result for 2>, <result for 3>,
etc.)

John W. Vinson[MVP]