It seems to me that each artist can have many drawings, and that each drawing
can have many entries in its history. I can't tell from your reply if you
have established relationships between these three tables. Cascade updating
or deleting related fields or records depends first on the relationships.
To set up a relationship the parent table needs a primary key. If the
artist table is the parent table and the drawing table the child table, and
if the artist's name changes, then any drawings by that artist can now be
associated with the artist's new name. The point is that you store the
primary key, not the artist's name in the related table (unless you want to
store the artist's name as it was at the time the artwork was created). If
you are using an autonumber primary key it makes no difference if you set up
the relationship to cascade update related fields. If, however, you are
using a value that could change then you need to check that option.
In your case I'm not sure why you would want to delete a record (assuming
the records are not accidental duplicates or something of the sort). If you
delete an artist record then every drawing by that artist will also be
deleted if you choose to cascade delete related records.
You say that you want to change the artist name on the form, but remember
that the form is only a sort of portal to the table. You are using the form
to modify the table. If you modify the artist's name in the artist table,
that change may subsequently be viewed in forms or reports.
If you need suggestions that apply specifically to your situation then
please post your table structure. You don't need to post a lot of details.
The names of the primary and foreign key fields, and a brief description of
the table's purpose (e.g. "Artist personal information") will be enough.
