• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

Option Group question MS Access

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.
  • 3
  • 2
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
dbfromnewjerseyAuthor Commented:
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.
Jeffrey CoachmanCommented:
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:
...here 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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
dbfromnewjerseyAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now