Option Group: Auto Update

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

Guest

How can I set an option group to auto update when new fields are created, and
keep the fields in alphabetical order?

I switched from a list box because the option group was more pleasing to
view, but now I have to manually update the fields in the option group each
time a new option is added, which is frequent.

Perhaps an option group is not the right control, but my experience is that
people are more apt to want to use the database the more visually pleasing it
is, and having the radial buttons looks sharp...from the feedback I've
received.
 
Option groups work great when there are a limited number of options.
Unfortunately, as you've also found with the listbox approach, both these
require considerable "real estate" on the screen as the list of options
grows.

An alternative is to use a combobox. It only takes up the space of a single
text box, but by basing it's row source on a table, the list of "options"
displayed in the combobox can grow (or shrink) as the situation changes.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
franklinbukoski said:
How can I set an option group to auto update when new fields are created, and
keep the fields in alphabetical order?

I switched from a list box because the option group was more pleasing to
view, but now I have to manually update the fields in the option group each
time a new option is added, which is frequent.

Perhaps an option group is not the right control, but my experience is that
people are more apt to want to use the database the more visually pleasing it
is, and having the radial buttons looks sharp...from the feedback I've
received.
Click to expand...

An option **group** only allows one option to be selected.
You must be talking about a bunch of option buttons or
something else.

If you do have a separate option button for each value and
want to add new option buttons when you add a new row (i.e.
new option) to a table, then you have a form design that is
data dependent. This causes all kinds of maintenance
problems as you are now seeing.

It would be best to find a design that is not data
dependent. One idea is to use a continuous subform with an
option button and a label in the detail section.

If you really must use a bunch of option buttons on your
existing form, then you could create a lot of unbound,
invisible option buttons. Then use code in the form's
Current event that determines which options to make visible,
position them appropriately, monitor all of them for user
input and save any changed values back to the options table.

OTOH, if this list of frequently changed options are
separate fields in each record in some table, then you are
in serious trouble and the problem you are asking about is
just the tip of a disasterous iceburg that will doom your
application to many more and more serious problems.
 
Thanks Jeff! Perhaps option groups will be modified in a future version to
accomodate this type of use.
 
The option group is definitely maintenance intense. However, when I switched
the control to a listbox, the listbox automatically populated all of the
correct data, to include updated rows when executed. I'm good and worried
now, so I'm going to research data dependant forms, etc... Being that the
listbox works without maintenance, I'm hoping I do not have this issue?

Perhaps I used the wrong term earlier, the individual rows don't change, but
they do increase in number.

I tried using a continuous subform with an option button and a label in the
detail section, but couldn't get the option button label to populate with the
data. The form just showed one button and a label that read Option0.

The concept of the database is a Question and Answer reference for work, the
startup page has a control that displays topics, upon selecting a topic a
subtopic populates in an adjacent control (listbox), upon selecting the
subtopic a question and answer subform becomes visible with records that can
be scrolled through.
 
How can I set an option group to auto update when new fields are created, and
keep the fields in alphabetical order?

I switched from a list box because the option group was more pleasing to
view, but now I have to manually update the fields in the option group each
time a new option is added, which is frequent.

Perhaps an option group is not the right control, but my experience is that
people are more apt to want to use the database the more visually pleasing it
is, and having the radial buttons looks sharp...from the feedback I've
received.
Click to expand...

An option group doesn't contain "fields" - it returns a numeric value, which
may (or may not) be displayed on the form with an arbitrary label.

There's no automated way to do what you ask, that I know of. You would need
VBA code to open the form in design view, resize the option group control,
create new radio (not radial!!) button controls, create new labels, create new
values for them... complex and probably unreliable code. What would you do if
it were necessary to REMOVE a button?


John W. Vinson [MVP]
 
Thank you for the correction! I hadn't considered what to do should a button
need to be removed. Posting this question really demonstrates my ignorance
and I'm quite embarrassed, but appreciative of all the feedback :-) Back to
the books I go!
 
The concept of the database is a Question and Answer reference for work, the
startup page has a control that displays topics, upon selecting a topic a
subtopic populates in an adjacent control (listbox), upon selecting the
subtopic a question and answer subform becomes visible with records that can
be scrolled through.
Click to expand...

A Listbox or Combo Box would be a much easier control to manage than an Option
Group. Option groups are best for static lists.

Suggestion: maybe base a Listbox on a query concatenating a circular bullet
type character and a vertical bar before the text? This would give somewhat of
a "button" appearance.

John W. Vinson [MVP]
 
franklinbukoski said:
The option group is definitely maintenance intense. However, when I switched
the control to a listbox, the listbox automatically populated all of the
correct data, to include updated rows when executed. I'm good and worried
now, so I'm going to research data dependant forms, etc... Being that the
listbox works without maintenance, I'm hoping I do not have this issue?

Perhaps I used the wrong term earlier, the individual rows don't change, but
they do increase in number.

I tried using a continuous subform with an option button and a label in the
detail section, but couldn't get the option button label to populate with the
data. The form just showed one button and a label that read Option0.

The concept of the database is a Question and Answer reference for work, the
startup page has a control that displays topics, upon selecting a topic a
subtopic populates in an adjacent control (listbox), upon selecting the
subtopic a question and answer subform becomes visible with records that can
be scrolled through.
Click to expand...


Depending on what your table(s?) contain, the continuous
subform can be easier or harder to put together. I would
expect to use a table with the description of each object
(and maybe a sorting value) and Join that to the options
table to populate the subform. The continuous subform may
be prettier but the functionality would be very similar to a
list box.

I should probably explain what i meant by a data dependent
form design. First, note that a list box (or continuous
subform) is not data dependent because its very nature
accommodates an arbitrary number of records with no design
changes.

What you were trying to do by having a separate control
(option button) for each value in a table is a highly data
dependent design. Whenever a record is added to the options
table, you have to make a corresponding change to the form's
design, which raised your original question. Taken to an
extreme, there isn't enough space on a monitor to cover all
the possibilities (making a combo/list box an ideal
alternative).

A more insidious example is when you want to count the
number of records with a particular option. So, you add a
text box with an expression like:
=Sum(IIf(field = "option1", 1, 0))
Now you are digging a hole because you have a data value in
the expression. Instead an Id value that users never see
should be used so the data can be changed without affecting
the form's design.
 
Thank you for the further clarification. The information shared on this
discussion group has been invaluable to me and is much appreciated.
 
Back
Top