Option Group Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We are trying to convert some of our information to new guidelines given to
us by our grantor. One of those things that was changed was that for
ethnicity status, we used to have Hispanic = 1, Non-Hispanic = 0, and I do
not wish to respond = 9. Now we have Hispanic = HY, Non-Hispanic=HN, and
Unknown = UN. (I know, gotta love gov't requirements!). Well I converted
the data in the table, however in the form I originally had an option group
w/ check boxes to select the appropriate response. Great, except now I have
letter codes instead of # codes and the option group won't let me have letter
codes. The table is set up to store the letter codes for each option. But
I'm trying to figure out how I can have check boxes to select the correct
response and still store the information in 1 field showing HN, HY, or UN.
Please help.
 
Aha! this sound suspiciously like WIA reporting to me. Did that for 4
counties in Oregon 5 years ago. Anyway, I suspect your option group is
bound to the field in the table that you have converted to text. Here is how
you can do that.
Make your option group unbound
Create a text box and bind it to the field and make the text box invisible
and tabs top = no
In the After Update event of your option group:

Select Case Me.opgEthic
Case is = 0
Me.txtEthnicity = "HN"
Case is = 1
Me.txtEthnicity = "HY
Case Else
Me.txtEthnicity = "UN"
End Select
 
Christina,

I would continue to use a field in your table for ethnicity which is a
number data type, and allow the use of an Option Group in your form for
the management of this data. And then I would make a simple table, 2
fields and 3 records, where you list the 3 ethnicity options, with their
numerical value in one field and the 2-letter code in the other. Ok,
then whenever you need the 2-letter code, presumably mainly for the
purpose of reports, you simply include this little lookup table in the
query that the report is based on, joined to the ethnicity field in the
main table, and hence you always have the 2-letter code available.
 
I tried incorporating the coding and here's what I have:
Private Sub Ethnicity_AfterUpdate()
Select Case Me.opgEthnicity
Case Is = 1
Me.txtEthnicity_Code = "HN"
Case Is = 2
Me.txtEthnicity_Code = "HY"
Case Else
Me.txtEthnicity_Code = "UN"
End Select
End Sub
Where 'Ethnicity' is the Option Group and 'Ethnicity_Code' is the text box
tied into the table.
When I try to use it, I get the following error:
'Compile Error: Method or Data Member Not Found'
Am I putting the wrong data in the wrong place?
 
This worked! Thanks
Christina

Steve Schapel said:
Christina,

I would continue to use a field in your table for ethnicity which is a
number data type, and allow the use of an Option Group in your form for
the management of this data. And then I would make a simple table, 2
fields and 3 records, where you list the 3 ethnicity options, with their
numerical value in one field and the 2-letter code in the other. Ok,
then whenever you need the 2-letter code, presumably mainly for the
purpose of reports, you simply include this little lookup table in the
query that the report is based on, joined to the ethnicity field in the
main table, and hence you always have the 2-letter code available.
Click to expand...
 
Why is the sub named Ethnicity_AfterUpdate, but you're looking at
Me.opgEthnicity?

If the name of the option group is Ethnicity, you should be looking at
Me.Ethnicity.

Conversely, if the option group is named opgEthnicity, then the sub should
be Private Sub opgEthnicity_AfterUpdate()
 
But, in any case (no pun intended) for this sort of approach to be
useful, you will also need code on for example the Current event of the
form, to re-translate the txtEthnicity_Code value back to the
corresponding value for the unbound option group.
 
Back
Top