Option Group value to string

[BigPig]

Hi all,

I don't know a lot about access, but I am very familiar with excel.

I'm trying to take the value of an option group (1,2,3) in a form, and
update a field in a table to a string (Yes, No, NA). I've tried the IIF
statement in "before update", but I'm not 100% how to get that to work.
"=IIf([fme_Priv_Health_Ins]=1,"Yes",IIf([fme_Priv_Health_Ins]=2,"No","NA"))"
I think that I have to tie it to the table and field that I want entered.

I've also read about referencing a table, so I created one "tbl_Yes_No_NA",
but I don't know how to reference it.

I thought about going into vba to do it, but I'm thinking there has to be a
way to do it without macros.

If anyone could point me in the right direction, it would be greatly
appreciated. Thanks.

 

[tina]

I'm trying to take the value of an option group (1,2,3) in a form, and

update a field in a table to a string (Yes, No, NA). I've tried the IIF
statement in "before update", but I'm not 100% how to get that to work.

"=IIf([fme_Priv_Health_Ins]=1,"Yes",IIf([fme_Priv_Health_Ins]=2,"No","NA"))"

assuming that the table you refer to above is the RecordSource of the form
which contains the option group, you can set the value of the field with
either a macro or VBA (which are not the same thing, in Access). in the
option group control's AfterUpdate event, add an Select Case statement, as

Select Case Me!fme_Priv_Health_Ins
Case 1
Me!FieldName = "Yes"
Case 2
Me!FieldName = "No"
Case Else
Me!FieldName = "N/A"
End Select

replace FieldName with the name of the field in the form's RecordSource
where you want the value to be saved. btw, you could use an If statement,
too, but i prefer Select Case for something like this.

hth

Hi all,

I don't know a lot about access, but I am very familiar with excel.

I'm trying to take the value of an option group (1,2,3) in a form, and
update a field in a table to a string (Yes, No, NA). I've tried the IIF
statement in "before update", but I'm not 100% how to get that to work.
"=IIf([fme_Priv_Health_Ins]=1,"Yes",IIf([fme_Priv_Health_Ins]=2,"No","NA"))"
I think that I have to tie it to the table and field that I want entered.

I've also read about referencing a table, so I created one "tbl_Yes_No_NA",
but I don't know how to reference it.

I thought about going into vba to do it, but I'm thinking there has to be a
way to do it without macros.

If anyone could point me in the right direction, it would be greatly
appreciated. Thanks.

 

[BigPig]

Hi tina,

Thanks for your help.

I put your suggestion in "code" versus "macro" in the after update event as
you mentioned. It works, but...

When I click one of the options in the option group with the associated
code, nothing is selected (none of the radio buttons get the black dot), but
in the table the appropriate yes-no or na is there.

Thanks again for your help.

 

[tina]

well, that's odd. normally the "clicked" option will be selected, unless you
run code to set the value of the option group contro to Null. are you seeing
this behavior immediately when you click an option? or when you first open
the form to an existing record? or when you move from one record to another?

hth

 

[BigPig]

Hi tina,

Yes, I see this happening upon clicking on an option. I think that it's
because my option groups were bound.

What I already started to do is I unbound all the option groups, and added a
cmd btn that adds records. Now I'm just trying to figure out how to add info
from the form to a recordset in a table.

What do you think?

 

[tina]

well, i can understand why you would not see a selection if the option group
control is bound to a field where you're saving text values (the "yes",
"no", "n/a" posted previously). the value of an option group control is a
Long Integer data type; there's no way it can translate those stored text
values into Long Integer values that are valid for the options (1, 2, 3) in
the group.

but unbinding a form from a table, and using a recordset to add table
records, just because of this option group issue, seems a bit extreme to me.
frankly, if this is a data entry form that will get frequent and/or heavy
use, i'd dump the option group control(s) in favor of combobox controls,
personally. though usually an option group can be navigated from the
keyboard, it's a bit cumbersome, and not intuitive. and if your user(s) are
looking for speed and accuracy in using the form for data entry, i can tell
you that both will suffer every time a user takes his/her hand off the
keyboard to use the mouse. i say this as an Access application developer,
and as a person who makes, and has made, her living doing data entry for
nearly a quarter century - from typewriters to early "dummy" terminals, to
PC keyboard with mouse for the last 14 years.

if an option group is appropriate to the kind of use the form will get, or
if you just have your heart set on it, suggest you create a supporting table
to list the options and their text "translation", as

tblOptions
OptID
OptText

example:

OptID OptText
1 Yes
2 No
3 NA

in your *data* table, set the field's data type to Number, field size Byte.
(even though the option group value is Long Integer, the actual numbers are
Byte size, so i think it will work okay. if not, change the field size to
Long Integer.) i'm talking here about the field that you have been using to
store the Yes/No/NA values. set a relationship from tblOptions to the data
table, one-to-many from OptID to the field in the data table. now, you can
bind the table to the data entry form, bind the option group control to the
field where you want the value stored, and get rid of the AfterUpdate code
we discussed earlier in this thread. the table will store the number values
1, 2, or 3 as selected from the option group; should be no problem. to see
the text values in reports, just link tblOptions to the data table in a
query, include field OptText in the query, and use that field in the report.

hth

 

[BigPig]

Hi tina,

Thank you very much for your thorough explanation.

I agree that it seems like a lot of extra work to unbind the option groups
etc, and I believe that you are right with using the comboboxes (thought
about that too).

The reason I went with the option groups is for the sake of ease on the
user. It seemed a lot quicker/easier to click "radio buttons" than to select
something from a combobox.

I did what I wrote last. Seems to work. This project was kind of last
minute, and I didn't have a lot of time to put it together. (not to mention I
don't know access that well, fortunately vba doesn't change much)

Thanks again for your help. I will use your suggestions in my future
projects.

 

[tina]

you're welcome. an additional note on combobox controls: you're thinking of
the time it takes to open and look at a droplist and possibly scroll the
list and then make a selection. for large lists, this is a consideration,
though it wouldn't be practical to use option groups for large lists anyway.
but in shorter lists, i usually make sure the selections can be entered by
one keystroke, or occasionally two, rarely by three. a frequent user will
learn the keystrokes for each selection and make them automatically; it's
very fast for data entry. so for the selections we've been discussing, the
user would simply enter the control, type "y" or "n" or "na", and move on.
and, of course, a combobox takes up at lot less form real estate than an
option group.

 

[BigPig]

Hi tina,

You're right, hadn't thought about that, and I totally agree (being a
keyboard user myself). Just kind of figured that most users are "point and
click". When I get to do a last minute project like this again, I'll
definitely use the comboboxes.

Thanks again.