Automatically Move Records

  • Thread starter Thread starter Sheri Emery
  • Start date Start date
S

Sheri Emery

Hi,
I'm wondering if Access can automatically (or at the push
of a button) move a record from one table to another. The
first table is the current table. In this table, I can
only have one client with their current supervision level
(the supervision levels change often, but my forms and
reports can only show their current level). I would like
all the other records to go to an archive table, so that
we can keep a history of level change activity. I set up
an append query and hooked it to run automatically on the
click of a command button. The command button also
deleted the current record. The problem I encountered is
that while deleting the current supervision record, it
also deleted the master client record (pulled from a
separate table). I need to keep the client record, and
just "move" the supervision record from one table to the
other, and I'm really stuck. I've looked through help and
several articles, but can't seem to find anything. Am I
just out of luck here?
 
Check the relationship between table that contains the current supervision
record and the table that contains the master client record. It sounds like
you have Cascade Delete checked. Uncheck Cascade Delete and the master
client record will not get deleted.
 
Thanks for the advice, but when I checked the Referential
Integrity in the relationships, Cascade delete was not
checked. Is there another place where this could be
occurring? Does it matter that my query join option is to
show all client records and only those supervision records
that have a matching client record?
Sheri
 
Q1 - There are three ways to delete a record: 1) Cascade Delete, 2) Delete
Query, 3) Code
Q2 - Your join option won't cause a record to be deleted

Are you sure the main client record is deleted from the table or is just not
appearing on your form?
 
Hi - Thanks again. It is definately deleting from the
main client table. Is there another way to access the
Cascade delete feature that I just haven't looked at?
Sheri
 
No, Cascade Delete is only set in a relationship. It must being deleted
either by a delete query or code.
 
Back
Top