Option Group storing non-numeric values

[domtrump]

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.

 

[xenou]

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.

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]

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.

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]

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]

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.

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]

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