Option groups value from a table field

[Guest]

Hi, I'm new to access and am hoping someone can help me out. I'm trying to
use an option group to set a value of a field in a table. The option group
has 3 options. "yes" "no" and "na". This seemed a better option than
individual check boxes and fields.

I have the group unbound on a form and want to have the result of the option
group set the table value to "yes" or the other repsonses. I have so far
managed using a floating text box on the form i.e..

IF me.optiongp = 1 then
me.txtbox = "yes" Elseif
me.optiongp = 2 then
me.txtbox = "no" else
me.txtbox = "na"
endif

I'm not sure on how to link directly to the field in the table, so thought
perhaps I could run an sql update using the txtbox to update the table field
(say tbltest.Response). If this is a messy way round it i'd love to hear a
quicker solution.

Finally the bit that is really puzzling me is I would like the unbound
option group to display the value for the associated table field
(tbltest.Response) when the form is opened. I thought this would be a reverse
of the above if statement but it doesn't work.

i.e
me.txtbox.rowsource = "select response from tbltest"
IF me.txtbox = "yes" then
me.optiongroup.value = 1 etc

The option group remains unchecked. Is there a simple way to have my option
group display what lies in the table and also allow for dynamic updating on
clicking? Each record has a ID as identifier so this would need including in
the above code, as you can see i'm unsure about it all!

Many thanks in advance for any help.
R

 

[Marshall Barton]

Comments inline below.
--
Marsh
MVP [MS Access]

Hi, I'm new to access and am hoping someone can help me out. I'm trying to
use an option group to set a value of a field in a table. The option group
has 3 options. "yes" "no" and "na". This seemed a better option than
individual check boxes and fields.

I have the group unbound on a form and want to have the result of the option
group set the table value to "yes" or the other repsonses. I have so far
managed using a floating text box on the form i.e..

IF me.optiongp = 1 then
me.txtbox = "yes" Elseif
me.optiongp = 2 then
me.txtbox = "no" else
me.txtbox = "na"
endif

I'm not sure on how to link directly to the field in the table, so thought
perhaps I could run an sql update using the txtbox to update the table field
(say tbltest.Response). If this is a messy way round it i'd love to hear a
quicker solution.

Here's a standard way to do what you asked. Just set the
text box's ControlSource to the name of the field in the
table. Then, use code like this in the option group's
AfterUpdate event procedure:

Select Case Me.OptionGp
Case 1
Me.txtbox = "Yes"
Case 2
Me.txtbox = "No"
Case 3
Me.txtbox = "na"
Case Else
Me.txtbox = "Unknown"
End Select

This is not especially messy, but see below for some
alternatives.

Finally the bit that is really puzzling me is I would like the unbound
option group to display the value for the associated table field
(tbltest.Response) when the form is opened. I thought this would be a reverse
of the above if statement but it doesn't work.

i.e
me.txtbox.rowsource = "select response from tbltest"
IF me.txtbox = "yes" then
me.optiongroup.value = 1 etc

The option group remains unchecked. Is there a simple way to have my option
group display what lies in the table and also allow for dynamic updating on
clicking? Each record has a ID as identifier so this would need including in
the above code, as you can see i'm unsure about it all!

A text box does not have a RowSource (that's for Combo and
List boxes). Once you have the text box bound to the field
in the table, you don't have to do anything to retrieve the
value. So the only thing to do is translate the value of
the text box back to the option group with code like this in
the form's Current event:

Select Case Me.txtbox
Case "Yes"
Me.OptionGp = 1
Case "No"
Me.OptionGp = 2
Case "na"
Me.OptionGp = 3
Case Else
Me.OptionGp = Null
End Select

This whole approch seems to be somewhat clumsy. I don't
understand why you want to have a text box with a
translation of the what the option group already displays??
Why not get rid of the text box and just bind the option
group to the field in the table? If you have a report or
something where the Yes/No/na values need to be displayed,
you can use the above code to do it.

A different user interface that you could use to do this
would be to use a little lookup table with three records
Yes, No and na and use that as the RowSource in a combo box
instead of uisng an Option Group. Then there would be no
need to translate either way.

 

[fredg]

Hi, I'm new to access and am hoping someone can help me out. I'm trying to
use an option group to set a value of a field in a table. The option group
has 3 options. "yes" "no" and "na". This seemed a better option than
individual check boxes and fields.

I have the group unbound on a form and want to have the result of the option
group set the table value to "yes" or the other repsonses. I have so far
managed using a floating text box on the form i.e..

IF me.optiongp = 1 then
me.txtbox = "yes" Elseif
me.optiongp = 2 then
me.txtbox = "no" else
me.txtbox = "na"
endif

I'm not sure on how to link directly to the field in the table, so thought
perhaps I could run an sql update using the txtbox to update the table field
(say tbltest.Response). If this is a messy way round it i'd love to hear a
quicker solution.

Finally the bit that is really puzzling me is I would like the unbound
option group to display the value for the associated table field
(tbltest.Response) when the form is opened. I thought this would be a reverse
of the above if statement but it doesn't work.

i.e
me.txtbox.rowsource = "select response from tbltest"
IF me.txtbox = "yes" then
me.optiongroup.value = 1 etc

The option group remains unchecked. Is there a simple way to have my option
group display what lies in the table and also allow for dynamic updating on
clicking? Each record has a ID as identifier so this would need including in
the above code, as you can see i'm unsure about it all!

Many thanks in advance for any help.
R

You're creating all of this difficulty for yourself by trying to
re-engineer Access.
This is all quite easy to do.

First, add a new field to your table.
Name it OptField
Datatype: Number
Field Size: Integer.
Make the Default Value property Blank.

Now open the form in design view.
Right-Click on the current Option Group (on the Frame, not on an
individual button inside the frame).
Set the Groups Control Source to:
OptField

Now when ever you click in the Option Group, the value selected (1, 2,
3, etc) will be saved in the OptField field. Each record stores it's
own OptField value.

To show text, instead of the stored number, in a form or in a report,
always use an unbound control.
Set the control's Control Source to:

=Choose([OptField],"Yes","No","na")

An alternative way to display text instead of the number value would
be to use an IIf statement in the unbound control's control source:

=IIf([OptField]= 1,"Yes",IIf([Optfield] = 2,"No","na"))

The above 2 methods require no coding in a sub procedure.
Just write the statement directly in the control's control source
property line.

By the way, regarding this line of your message:

me.txtbox.rowsource = "select response from tbltest" <

Text controls do not have a RowSource, then have a control source.
You cannot use a SQL statement ("Select response from tbltest")
directly in a control's control source.

I hope this helps you to an easier day.