Option Group storing non-numeric values

domtrump

Board Regular
Joined
Apr 1, 2010
Messages
245
I am new to creating forms with Access 2007. I am trying to create an option group with three option buttons. It is bound to a text field (Color) that has the look up set with 'row source type' = Value List and my three values listed in 'Row Source'.

Let's say the three possible values are Red, Green & Blue. When the user clicks one of the radio buttons, I want the text value of Red, Green or Blue stored in the Color field. However, it seems like an option group will only store numeric values (1,2,3 etc.).

How can I have an option group that stores actual values in the table? (Note: would prefer a non-VBA method if possible).

Thanks.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
1) A listbox or combobox would probably work better if you want to bind the control directly to the table.

2) Another possibility would be to actually use numbers in the table and have a color lookup table (1-Red, 2-Green, 3-Blue, etc) to provide the values of these color ID's in your queries and reports. Then both option group and field are numeric.

3) Finally, you can use the after update event of the control to update the color field. In this manner, the option button still keeps its value of 1,2,3 etc. But you synch the field to match the option chosen. You'd probably make the field to be hidden - the user only sees the option group buttons. This is typical of option groups (except when a field has a small set of integer values in its domain), so you can't exclude vba if you intend to employ option groups.

Code:
Private Sub MyOptionGroup_AfterUpdate()
    Select Case Me.MyOptionGroup.Value
        Case 1
            Me.MyField.Value = "Red"
        Case 2
            Me.MyField.Value = "Green"
        Case 3
            Me.MyField.Value = "Blue"
    End Select
End Sub
 

domtrump

Board Regular
Joined
Apr 1, 2010
Messages
245
1) A listbox or combobox would probably work better if you want to bind the control directly to the table.

2) Another possibility would be to actually use numbers in the table and have a color lookup table (1-Red, 2-Green, 3-Blue, etc) to provide the values of these color ID's in your queries and reports. Then both option group and field are numeric.

3) Finally, you can use the after update event of the control to update the color field. In this manner, the option button still keeps its value of 1,2,3 etc. But you synch the field to match the option chosen. You'd probably make the field to be hidden - the user only sees the option group buttons. This is typical of option groups (except when a field has a small set of integer values in its domain), so you can't exclude vba if you intend to employ option groups.

Code:
Private Sub MyOptionGroup_AfterUpdate()
    Select Case Me.MyOptionGroup.Value
        Case 1
            Me.MyField.Value = "Red"
        Case 2
            Me.MyField.Value = "Green"
        Case 3
            Me.MyField.Value = "Blue"
    End Select
End Sub
Thanks. Just to clarify.... in the case of using VBA, I'd have two fields. One to store the numeric value and one to store the actual color. I would not display the color field on the form... just the option group.

Is this correct?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Yep. The "real" field is the color field (text, hidden). The user sees the option group. When the user makes a change in the option group, you use the after update event of the option group control to update the hidden field to the correct (text) value. This probably assumes a data entry type of form, or one where you are editing a record.

ξ
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,120
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
1) A listbox or combobox would probably work better if you want to bind the control directly to the table.

2) Another possibility would be to actually use numbers in the table and have a color lookup table (1-Red, 2-Green, 3-Blue, etc) to provide the values of these color ID's in your queries and reports. Then both option group and field are numeric.

3) Finally, you can use the after update event of the control to update the color field. In this manner, the option button still keeps its value of 1,2,3 etc. But you synch the field to match the option chosen. You'd probably make the field to be hidden - the user only sees the option group buttons. This is typical of option groups (except when a field has a small set of integer values in its domain), so you can't exclude vba if you intend to employ option groups.

Code:
Private Sub MyOptionGroup_AfterUpdate()
    Select Case Me.MyOptionGroup.Value
        Case 1
            Me.MyField.Value = "Red"
        Case 2
            Me.MyField.Value = "Green"
        Case 3
            Me.MyField.Value = "Blue"
    End Select
End Sub

Could you please clarify some stuff for me. I need some step by step instructions.

I have Option Groups in my Form. The User selects "Y", "N" & "TBD", but in my Table based on the selection it shows as either "1" "2" or "3". So what specifically do I need to do to get this code to work in my Form? I'm not sure what you mean by "make the field to be hidden." What other field are you referring to? Is this referring hidding a field in my source Table?

Thanks.
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,120
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Could you please clarify some stuff for me. I need some step by step instructions.

I have Option Groups in my Form. The User selects "Y", "N" & "TBD", but in my Table based on the selection it shows as either "1" "2" or "3". So what specifically do I need to do to get this code to work in my Form? I'm not sure what you mean by "make the field to be hidden." What other field are you referring to? Is this referring hidding a field in my source Table?

Thanks.

Nevermind. Found Instructions on here:

How to change Option Group value (from # to text) - Microsoft Access / VBA
 

Forum statistics

Threads
1,136,799
Messages
5,677,807
Members
419,721
Latest member
StuckInWork

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