Option Group Radio Buttons: Number to Text using Visual Basic

[mcw176]

I am attempting to save information from option group radio buttons into the form's table and the underlying table as text instead of numbers. I have been moderately successful so far; as long as the user clicks the "Enable Content" button when Access opens, when they click the specific radio button, that text will be entered into both the form's table and the underlying table. However, the button grays out - there is no evidence that it was clicked.


I would like to have the button appear and stay selected.
My option group is named StudyTypeFrame, and the Control Source is the field StudyType on the underlying table. My code looks like this:

Private Sub StudyTypeFrame_AfterUpdate()
Select Case Me.StudyTypeFrame
Case 1
Me.StudyType = "GN Clinical Trial Research"
Case 2
Me.StudyType = "SNN Clinical Trial Research"
Case 3
Me.StudyType = "Clinical Research"
End Select
End Sub

I am very new to Access, and beyond adapting this code for my form after finding something similar in one of the forums, I have no coding experience. Any advice would be wonderful!

If the radio button->text association is too impractical, I have another question. If the radio buttons save numbers as they normally do, is it possible to use those numbers to determine text in another field? For example, if button "1" is clicked and the underlying table displays the "1", is there a way to say "if [fieldname a] = 1, then [fieldname b] = text1" ?

Thank you so much!

[June7]

Don't understand why the button greys out.

Yes, an expression in textbox can return alias, like:

=Choose([StudyType], "GN Clinical Trial Research", "SNN Clinical Trial Research", "Clinical Research")

An alternative is a combobox or listbox with the 3 options.

[Dal Jeanis]

First, if the underlying field is numeric, as you were suggesting, then just change the frame's ControlSource to that field. Assuming the wording of the Radio Buttons is your three different values, then you're done. Get rid of the AfterUpdate code. (The Frame may have been greying out because it isn't bound to anything.)

Typically, you would also add a small table to your database that has two fields - the key value (1-3) and the wording for that item. This will help you put the words on a report (rather than the key numbers) using a lookup feature or a join. for example, the table could look like this:

tblStudyType
StID      PK (primary Key - 1 to 3)
StName    TEXT (the names)

Or, you could use June7's code everywhere you want to translate back from the number.

Once you have that table, you could also replace the radio buttons with a combobox. The combo box would be bound to the underlying field, have two columns, and you'd set the properties like this

Column Count     2
Column Width     0";2"
Control Source   [StudyType]   (name of the underlying field)
Row Source to    Select tST.[StID], tST.[StName] from tblStudyType AS tST ORDER BY tST.[StID];

[June7]

A combo or list box can also have a Value List as RowSource, table not needed. Not advised if the list frequently changes.

[mcw176]

Thank you June7 and Dal Jeanis!

June7, I did use your code in a text box in the form, but I would have wanted to link that back into the table as well, and I wasn't sure how to do that; I decided to try a second option before I asked.

Dal Jeanis, I followed your advice about the second table. I created a one-to-many relationship between the Primary Key of the StudyType table and the StudyType# stored by the radio buttons in the main table. I then created a query incorporating the two. Finally, when I was writing my report, I drew fields from the two different tables as necessary. I can't say that I fully understand why it works, but I have a basic idea and I'm pleased with the result.

I appreciate you're advice about the combo boxes -- I've used several already, and I know they would be SO much easier, but my boss said she prefered the aesthetic of the buttons.

Thank you two so much - I'm so glad I was able to figure everything out this quickly