If I set a relationship in access to allow cascading deletes is there anyway to add a clause that doesnt delete certain things.
what i want to do is if i delete a users record from the users table i want to automatically delete all their messages from the messages table unless they are in a certain category.
I can do it with SQL by passing the Users.fldusername value as a URL variable - varUsername

DELETE * FROM Messages WHERE Messages.flduser = 'varUsername' AND Messages.fldCategory <>'FOUND'

but I wondered if i can set these parameters in the database itself.

ASP|VBScript|IIS5.1|Access|WinXPPro & WinXPHome

BUMP

Any ideas??

ASP|VBScript|IIS5.1|Access|WinXPPro & WinXPHome

I've had this same problem... I <b>NEVER EVER</b> use cascading deletes (my boss would kill me if i did), instead I transfer ownership of the articles, or uploaded documents in my case, to another user. You could do that too, but if a relation exists to that userID you can NEVER delete the user in question, you get error a related record exists in blah blah...

I would use a

UPDATE Messages
SET (flduser)
VALUES (varNewUsername)
WHERE flduser = varusername

I assume you know how to translate that to working code, i'm not sure if that suits your need though, you could also set userActive to NO and add appropriate filters in your queries.

with regards,

Dennis van Galen
DMXzone Manager

Extension, Tutorial and FAQ's Manager

Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

Thanks Dennis

What I've done at the moment is set up a recordset to show me the users messages, so that I can review each one individually and then delete if needed.
I may look at this again sometime in the future though so thanks for your input.

Dave

ASP|VBScript|IIS5.1|Access|WinXPPro & WinXPHome