Define Option Group by Lookup Table

[bw]

I'm curious to know if this can be done, and if so, how.

I'd like to have a dynamic option group (with radio buttons) which grows and
shrinks by the values in a "lookup" table for this group.

I imagine a table having a number as the tableID which would represent the
value of the items in the option group, and a name field of the table as the
name of the items in the option group. If I add a number and name to the
"lookup" table, the option group would expand by one to include the new
entry in the table (or shrink by one if I were to delete an item in the
table).

The option group would of course be bound to a field in the underlying table
for this form.

Thanks,
Bernie

 

[Marshall Barton]

I'm curious to know if this can be done, and if so, how.

I'd like to have a dynamic option group (with radio buttons) which grows and
shrinks by the values in a "lookup" table for this group.

I imagine a table having a number as the tableID which would represent the
value of the items in the option group, and a name field of the table as the
name of the items in the option group. If I add a number and name to the
"lookup" table, the option group would expand by one to include the new
entry in the table (or shrink by one if I were to delete an item in the
table).

The option group would of course be bound to a field in the underlying table
for this form.

You can not add or remove controls from a form or report
when your application is running.

The standard approach to this lind of thing is to have as
many controls as you will ever need and make the ones you
don't need invisible. To get the option group to "grow or
shrink", you will need to set the invisible ones Top
property to 0 and the visible ones Top to an appropriate
value to place it where you want it.

Let's name the option buttons opt1, opt2, etc. so it will be
easier to refer to them in a loop. I will aslo assume the
you have variables that contain the "table" name for the
items in the option group.

Given all that and some other minor details that you should
be able to spot, here's some off the cuff air code that
demonstrates the idea:

strSQL = "SELECT * FROM OptionsTable " _
& "WHERE [tablename] = """ & strtable _
& """ ORDER BY OptValue"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
k = 1
lngTop = Me.optionframe.Top + 150
Do Until rs.EOF
With Me("opt" & k)
.Visible = True
.OptionValue = rs!OptValue
.Top = lngTop
lngTop = lngTop + .Height+ 100
k = k + 1
End With
Loop
For k = k To Me.optionframe.Controls.Count
With Me("opt" & k)
.Visible = False
.Top = 0
End With
Next k
Me.optionframe.Height = lngTop