Binding a control source at "runtime"--can't assign value.

[Guest]

I have a checkbox that potentially control any one of six different yes/no
fields in a table. The field it's actually bound to depends on a value that
is not known until the form is opened. In the form's OnLoad event, I check
this value and assign the checkbox the corresponding control source. Later
on, however, something happens in the form that makes me want to change the
value of that checkbox programatically. When I try to do this, I get an
error saying I "can't assign a value to this object." Why not?!

Thanks for any ideas.

-ndalton

 

[Allen Browne]

In general it is not a good idea to reassign the ControlSource of a bound
control after the form has opened.

Attempting to do so is likely to fail if any of the following apply:
a) the record is dirty (edit in progress);
b) the name of the control is the same as the name of one of the fields;
c) the data type of the field you are assigning is different than the data
type of the field it was previously bound to;
d) Name AutoCorrect is turned on (Tools | Options | General.)

It is actually quite easy to crash Access (shut down by Windows) if fields
come and go like that.

An alternative would be to provide check boxes for all the relevant fields,
and set their Visible property to show/hide them.

This whole scenario suggests that you might be better off creating another
table where you store a related *record* for each option that applies,
rather than using 6 yes/no fields in the table.

 

[Guest]

Good points. I shall heed them. Thanks.