Cascading updates/deletes

[Jim Hess]

Using Access XP. I have a "legacy" Access database
application that started in Access 97, and has migrated
forward. It is an accounting application that is used
within our department at the University. I have a form
for requisitions that is a master/detail form. The key
field in the main requisition table is the requisition
number, and that number is the foreign key in the line
items table. I have the relationship established in the
back end database, and I have cascading updates and
deletes selected. It seems that the updating has failed
because if I change a requisition number then the line
items are lost. This type of relationship still works
with the travel authorizations, but I can't seem to get it
working with the requisitions. Can someone please give me
a clue as to where to start? I haven't used any code for
the cascading updates or deletes. I have just relied on
the relationship to do the job for me. It has worked well
for several years, but now I can't seem to get it to work.

 

[Albert D. Kallal]

It seems that the updating has failed
because if I change a requisition number then the line
items are lost.

Did the above work before...and now it is not? If it always worked..and all
of a sudden stopped, then I would suspect that the file, or a index has
become damaged.

I would either:

a) try a compact, and then do a repair on the file
b) create a blank mdb..and import everything.

The above assumes that this was working...and now has stopped working..

 

[Jim Hess]

Thank you for the response. Yes, it did work before. I
might add that it seemed to stop working while I was away
for a few days during the holidays. When I returned to
work the one clerk had changed several requisition
numbers, and there were 109 unrelated line items that we
had to delete. I have compacted/repaired the database; I
have created new blank databases and copied the data into
the two tables. But I'm still having the problem. It has
been awhile since I did any new development with Access,
so there is the likelihood that I have forgotten
something. Is there a limitation that you cannot have
cascading updates/deletes on a key field?

 

[Joe Williams]

Check the relationship. Are you enforcing referential integrity between the
two tables? If so, this could never happen. Maybe that setting was changed
or was never enforced to begin with.

joe

 

[Jim Hess]

When this problem was brought to my attention, that was
the first thing I checked in the back end database because
that is where I had defined all the relationships. To my
surprise, there was not even a relationship shown between
requisitions and line items. I re-established the
relationship, and then enforced referential integrity.
But it still loses the line items if the requisition No.
is changed.