Option Group question MS Access

Posted on 2012-09-10
Last Modified: 2012-11-19
I have an option group on my Access Form.   I want it to be associated with record number 2 in my table  (Field1 is my primary key field and the records are numbered 1, 2, 3, 4, 5, etc)

When I make a selection in the option group (I'm using checkboxes), I want the selection to be written to MyTable.

So, for example, let's say my option group has 3 checkboxes; namely "yes", "no" and "maybe".  If I select the checkbox next to "no", I want the text string "no" written to Field2.

By the way, I don't care if it's better to have the record written or updated upon a change in the option group selection or by pressing a command button after making a selection. I just want the data to be written to the table. Thank you.
Question by:dbfromnewjersey
    LVL 84
    What do you mean by "record number 2"? Do you mean the second RECORD in your table, or the second FIELD in your table?

    Assuming you mean the second FIELD, then just bind the option group to that Field (the option group itself, not the members of that group).

    Note that this is a Numeric value, so it won't write "yes" or "no" to the field, but rather 0, 1 or -1.

    Author Comment

    The second RECORD in my table is associated with the option group.  Basically what it is is I have existing records in my table numbered 1 thru 10.   Field2 of each of those records is currently blank. I want to populate them.  In the particular case of record number 2, I want to populate field2 of record number 2 with the (text) value selected in the option group. So, if "no" is selected by checking the box next to the word "no" in the option group, I want "no" written to field2 of record number 2. If the user changes his mind and selects "yes", I want "no" to be overwritten with "yes" in field2 of record number 2.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    First, what tyou are descibing here is not an option group.
    With and option group you can select more than one option:
    Select your uses for this product:
    Other you can select Home *and* Work.

    If your actual selections are Yes, No and Maybe, ...then an option group is the wrong control for this.
    (Because you should not be able to selet Yes and No at the same time)

    This would be better if you just use a combobox bound to the field.

    Besides,  an optiopn grout requires much more maintenance if values are ever added changed or deleted.
    LVL 84

    Accepted Solution

    The second RECORD in my table is associated with the option group.
    So when the user is on the first or third record in the form, the option group is not available? Sorry, but I don't really understand what you're trying to do.

    If you want to use VBA to write this, then use code like this in the AfterUpdate event of the Option Group itself:

    Select Case Me.OptionGroup.Value
      Case 1
        '/ user selected the Yes value
        Currentdb.Execute "UPDATE YourTable SET YourField='yes' WHERE YourRecordID=1"
      Case 2
        '/ user selected the No value
        Currentdb.Execute "UPDATE YourTable SET YourField='no' WHERE YourRecordID=1"
    End Select

    Obviously you'd have to change that to match the structure of your database, and "YourField" would have to be a text field
    Jeff is correct in that often an Option Group is used for multiple choices, but in Access the default is to allow a single choice from that Option Group. You can build a multi-choice group, of course.

    Author Comment

    Directly from Access:

    "An option group contains a set of option buttons, check boxes, or toggle buttons. YOU CAN CHOOSE ONLY ONE OPTION."

    I have an OPTION GROUP on my form with check boxes. ONLY ONE CHECKBOX CAN BE SELECTED.

    I have an Access table with 2 fields.  Field1 is already populated with record numbers. It is the key field. Field2 is currently blank.

    I want RECORD NUMBER 2 associated with the option group.  When a user makes a selection in the option group, I want that selection written to Field2  of Record Number 2

    Let's say the table currently looks like this:

    Field1      Field2

    Let's say I have an option group with checkboxes that looks like this:

    (checkbox here)    yes
    (checkbox here)    no
    (checkbox here)    maybe

    Let's say the user selects "maybe". I then want the table to look like this:

    Field1    Field2
    2            maybe

    Thank you
    LVL 84
    Then you can use code similar to what I posted. Since you must always update record #2 in the table, you have to use VBA - unless your forms is ALWAYS on Record #2, that is.

    I must say this is a very non-normal way to do this. Exactly what are you storing, and why must you store the Option Group selection in Record #2?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now