Can I update an option group in a form based on query?

  • Thread starter Thread starter vufltrn
  • Start date Start date
V

vufltrn

I have a form which is used to review and enter new data. On that form
is an option group that shows if a certain procedure has been
performed. I am trying to get this option group to show if the
procedure has been performed, and it also be updatable.

LenProc1 is a value in the query the form is based on which looks at a
table (tblProcedure1), and if there is data it returns a value of '1',
otherwise '2'. On my form, I have an option group that I would like to
show this value, but be able to click the other option. In the After
Update of the option group, if the value is '1', it opens another form
to enter data on tblProcedure1; if the value is '2', it runs an SQL to
delete the data from tblProcedure1.

If I set the control source of the option group to LenProc1, I cannot
update the option group. If I leave it unbound, and put the default
value as =LenProc1, it works on the first record, but when the record
moves to the next record it doesn't update.

Is there a way to accomplish both of these?

Thanks for any help possible
 
Hi

what you're trying to do falls neatly between the two options for
controls in Access - bound (i.e. controlsource set to a field or
function) and unbound (blank controlsource). If you bind the control
to LenProc1 using the ControlSource, you can't edit it, because
LenProc1 is a calculated column, not a "real" column in a table - these
can't be edited.

The DefaultValue property only comes into play when the form opens, and
when you move to a new record - moving to a different existing record
won't result in Access evaluating the DefaultValue again according to
the current record's data. So blank out this property.

One solution is to set the option group to LenProc1 in VBA code, on the
Current event. (make sure the Controlsource is blank).

Sub Form_Current()
Me.TheOptionGroup.Value=Me!LenProc1
End Sub

If you do this, clicking the option group will have no effect on data
in the table or query (the control is unbound), but the opt group's
events will still fire, allowing you to open another form or run SQL.

hope this helps.

cheers

Seb
 
Vufltrn,

The Default Value property only applies at the point where a new record
is being created, so is not applicable to already existing records.

I think this should do what you are after - put code similar to the
following on the Current event of the form...
Me.NameOfOptionGroup = Me.LenProc1
 
Back
Top