I hope I'm not trying to do something untoward. I am developing a
small DB that has an entity, say Universities, each of which has a
many to many relation with another entity (Persons) that is resolved
through a one-one relation (Alumni). Some Universities have multiple
campuses, others do not. In both the Alumni and the University tables,
there's a field for "campus". By convention this field must be null in
at least one of the tables, i.e., if the University has a single
campus, it is specified in the University table and may not be
specified in the Alumni table. (I won't get into a long explanation,
but if it is null in the University, it may be specified with the
Alumni, but is still optional.)

I put the Alumni list in a continuous subform with the Person form,
one entry for each University the Person attended. I want the user to
be able to edit part of the list (the campus and department but not
the University). I want to fill in the campus from the University, if
it is specified there and prevent the user from editing it, if so. So
far no problem. But when I go to update the record, if it is dirty
(say the user changed the department), if the campus was inherited
from the University, I want to set it in the Alumni table to NULL not
what's in the control! I don't want to change what's displayed! If
only Jet SQL had stored procedures (esp. triggers), this would be no
problem, but that would be too kind of Microsoft.

Any ideas? (I am using Access 2000 and am new to it, in fact I am new
to programming MS stuff in general. I come from the Unix world.)

You can do this in the "Before_Update" event.
You may also need to look at the "On_Change" events for the fields.
Declare a few flags in the general section of the form code.
In the On_current event set them all to false. Also store the current
campus in a variable declared in the general section
In the on_change event for the fields of interest you can set the flag
for that field to true.
In the before_update event you can see which fields changed and decide
what to do. You can simply restore the campus to the value you saved.

You can also make the campus non-editable on the form.

Good Luck
Ira Solomon

On 31 Oct 2003 12:38:02 -0800, mmodica (AT) earthlink (DOT) net (Michael M.)
wrote: