Solved

Changing Option group Values to Text from Numerical Values

Posted on 2009-05-12
17
1,205 Views
Last Modified: 2013-11-28
In Microsoft Access 2003:

I have an option group that when one is selected it saves a number value in the control source. I need to specify a text value to correspond with the number value. For example, if Option 1 is selected it should save the value "Cat", "Dog" for Option 2, and "Fish" for Option 3 for reporting purposes. Right now, it will just save the number value 1, 2, and 3 which makes no sense to the end user when used in a report.

I am sure this is a common situation, and a quick answer would be best. 500 Points because time is of the essence!
0
Comment
Question by:epiclandsolutions
  • 7
  • 4
  • 4
  • +1
17 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24369218
Is your option group populated by a query or table or is it populated from values you typed in?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24369236
First things first,

You should have a table that lists the "Number" and the Text Value.
This way the textvalue can be looked up and displayed.

Or...

You can do an IF THEN Else as well:
If optX=1 Then
    SomeTextBox="Cat"
ElseIf optX=2 then
    Sometextbox="Dog"
End If

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24369389
epiclandsolutions,

You "DO NOT" save the text value, this violates basic database theory.
You store the number and "Lookup" the text value.

Besides, as far as I can tell from what you posted, a combobox would be a better design choice anyway, as it will save space if you have many choices.

There are dozens of ways to do what you are asking, here are two.
- Combobox in the form
- Query as the siource for the Report.

Niether of these standard techniques "Stores" the text value.

Here is a good book on database and table design
http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201694719

Here is a sample database:

JeffCoachman
db14.mdb
0
 

Author Comment

by:epiclandsolutions
ID: 24369444
Here is how my backend is setup:

I have tblTenant that holds the control sources for the Option Groups like the field "Question4a". Here is what i would need for the field "Question4"

1 - Bus
2 - Max
3 - Cab
4 - Walk
5 - Tri-met
6 - Other

boaq, where would I put that second code in my form, and how would it look with the above information?

cwood-wm-com, does this answer your question?

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24369558
If "Question4a" is your option group, you can use code something like the snippet. I have used Question4 as the name of the text box bound to your field Question4.
Select Case Question4a

    Case 1

        Question4 = "Bus"

    Case 2

        Question4 = "Max"

    Case 3

        Question4 = "Cab"

    Case 4

        Question4 = "Walk"

    Case 5

        Question4 = "Tri-met"

    Case 6

        Question4 = "Other"

End Select

Open in new window

0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 24369581
assuming you have a textbox to hold the value txtOptionValue

Private Sub OptionGroupName_AfterUpdate()
    Me! txtOptionValue = Choose( _
     Me![OptionGroupName], "Bus", "Max", "Cab", "Walk","Tri-met","Other")
End Sub

change OptionGroupName with the actual name of the Option group
0
 

Author Comment

by:epiclandsolutions
ID: 24369594
boaq2000,
I got your code working, and this will work. In the future what would be the best way to go about this from the start?

Also, thank you for the book recomendation :)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24369611

why do you have to change the option group, when you can make use of it properly!!
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:epiclandsolutions
ID: 24369719
capricorn1:
Well i posted that before I saw your response.

Upon glancing over the three solutions proposed by each of you, capricorn's seems to be the easiest to implament for more than one question.

Thank you all for your answers!
0
 

Author Comment

by:epiclandsolutions
ID: 24369742
Here is the final equation that worked for me:
Private Sub OptionGroupName_AfterUpdate()

    Me!txtOptionValue = Choose(Me![OptionGroupName], "Bus", "Max", "Cab", "Walk","Tri-met","Other")

End Sub

Open in new window

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24369869
Your solution looks exactly like capricorn1's solution, except for the underscore.
0
 

Author Comment

by:epiclandsolutions
ID: 24370030
there was also a space that was in there. I reposted the change so if someone was looking for the answer they could find it without having to trouble shoot the space and underscore. I still awarded points.
0
 

Author Closing Comment

by:epiclandsolutions
ID: 31580723
Private Sub OptionGroupName_AfterUpdate()
    Me!txtOptionValue = Choose(Me![OptionGroupName], "Bus", "Max", "Cab", "Walk","Tri-met","Other")
End Sub
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24370051
Using a space and an underscore as a line continuation is standard practice, but I see what you mean and you did award the points. Sorry for butting in.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24370472
<I got your code working, and this will work. In the future what would be the best way to go about this from the start?>

Are you still interested in this?
0
 

Author Comment

by:epiclandsolutions
ID: 24370494
boaq2000,

I would be willing to start a new question for that query, would you be interested in that?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24370569
No need.

Basically you will do pretty muh as I did in my sample.
Create a Table with the Number value and the Text Value.

This way both fields are available.
Then you will always reference the Numeric value, but display the text Value.

After that you can create whatever graphical interface you want.
I choes a combobx because it takes up less space in the form.
(imaging if you had 27 animal types, and an option button for each one!)

The reason why you always reference the numeric field is:
1. It is easire to refer to a numeric Filed in Code and/or SQL, than it is to refeence a text field.
2. You can have two Text values that are the same (two companies with the same name), but the Numeric Field will make them unique.

This is technique is deeply rooted in Standard Database theory (Normalization, Table relationships)
This is why I posed the link to the book.

If you still want to open a new question, that is fine by me.  Just post the link here, so I can find it.

JeffCoachman
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now