Delete records in other tables when one selection is chosen?

[Allison]

Access 2003, Win XP SP 3

I have a field in tblContacts called fldInactive. This is a Yes/No field.

I also have several other table relations where the unique fldContactID has
been associated with other related tables. One example is
"tblComboContactType", which contains a record that captures ContactID, and a
classification ID combination.

I want to delete all of the related associations for the ContactID when
someone indicates that the contact is now inactive.

I'm guessing this will have to look at every table except tblContacts, then
delete any record that contains the ContactID?

How would I do this?

Allison

 

[dymondjack]

If you have one-to-many relationships set up with cascading deletes enabled,
you should be able to delete the parent record and have all related records
deleted with them as well.

Another way would be to query for records that contacts your ContactID that
is now inactive.

Dim lngID as Long
lngID = 1 'Or whatever ID you want to delete

"DELETE * FROM tblComboContactType WHERE fldContactID = " & lngID

This would delete any records from the said table that has a matching
contact ID.

hth

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Allison]

I do not want to delete the parent, only make it inactive.

Thank you for the code. Since I want to trigger this action when the
fldInactive is changed to No, would I put this in the field's "after update"
event?

Allison

 

[Allison]

I replied just a second ago saying I didn't want to delete the parent. But,
maybe I do? Is it possible to capture the data being deleted from the table
and then write that to a different table?

I'm guessing I could then go ahead and delete the parent (and all its
children) but still keep the info I want in that different table. Is that a
better way? How would I do that?

Allison

 

[dymondjack]

There's actually quite a few different ways to handle this.

Many people opt to keep one field as a flag for the status. I do this, it's
either the second or third field in each table, and I call it fldStatus, of
the Byte datatype (less memory than Yes/No, among a few other complications).
Anyway, 1 = Active, 0 - inactive. I keep this indexed, and then each time I
use a form it queries for only active records (WHERE fldStatus = 1).

This keeps all your data in one table, and any detail records for an
inactive record won't be viewed by the user, as it's parent isn't part of the
recordset that they deal with. It makes it easy to retain records of all
contacts, either active or inactive.

Another advantage is, say you do a job for a customer with an active
contact, and 6mo later that contact is no longer active. But now, you may
want to see some history on that job, and even though the contact isn't
active, you may still want to see what contact it was, whether its still
active or not. Keeping both inactive and active contacts in the same tables
lets you do this without any special workarounds. (this scenario becomes an
issue when changes are made to data as well... sometimes you want to see the
original, rather than the changed).


Yet another option is to write these inactive records to an archive table.
Here's some info on that

http://www.allenbrowne.com/ser-37.html




--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Dale Fye]

As Jack indicates, cascading deletes would remove the records if you were
actually deleting the parent record, and have the relationships setup
properly.

As he mentions in his later post, I would probably NOT delete the records
from the 1-to-M tables, because I would not want to have to replace that data
if I reactivate the contact.

By filtering the inactive contacts out of your reports or other queries, you
can keep the data. Unless you are running out of database space (2Gig) for
later versions of Access, I would not delete the subordinate records.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Allison]

Thank you to both you and Dale Fye. I hadn't even considered filtering for
inactive. What a timesaver!

Allison

 

[dymondjack]

When you do this, using a yes/no field, just be wary that the options for
values on yes/no fields are actually Yes, No, and Null.

So, if you have this field and by default it is set to no (unchecked, any),
be aware that when using a query, you will have to include Is Null to
effectively catch these records as well.

This is the reason I use a byte datatype, and they are the only fields where
I set a default value to 0.


Here's some more info on it:
http://www.allenbrowne.com/NoYesNo.html

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[dymondjack]

When you do this, using a yes/no field, just be wary that the options for

values on yes/no fields are actually Yes, No, and Null.

So, if you have this field and by default it is set to no (unchecked, any),
be aware that when using a query, you will have to include Is Null to
effectively catch these records as well.

Disregard that... it's been a while and I knew there was a reason I didn't
use them, but had my info backwords


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain