Enforce Relationship rules as if deleting record

[Kempes]

Hello again,

Does anyone know if this is possible and is there an easy way to set it up?

My db has many relationships, hence if you attempt to delete a record from the table, it will not allow it if there are related records.

This is great, however.

My intention is to disable any record deletions. Instead, once a record is no longer live, the status of that record is set to disposed,exipired, etc.

I want to force the same rules as if attempting to delete this record but set to the status field.

Example message.
ie, Warning! You cannot dispose of this PC. There a related records in the Software License and Contracts Tables. Please reassign them and try again!

Any clues would be great.

Thanks All.

 

[The_Doc_Man]

1. You can only protect your system in the way you want if you force folks to use forms to do their work. You need to have Events to fire in order to do pre-action checks. If you allow persons to work from a Table or Query, you are hosed before you start because there are no Query or Table events to be fired.

So if you hadn't already done so, look into setting up everyone to use a startup form that is basically a switchboard. To allow you and other Access-savvy admins to bypass the switchboard, you can include a check for current user's group being Admin to enable/disable a button that says - Open main Window - or something like that.

The Search button on this forum works great. Look up "switchboard" as a topic and you'll get lots of responses.

2. There are all sorts of things you can do once everyone is locked into a form. If all actions are now controlled by buttons on a form, you have OnClick routines that can run any number of queries behind the scenes BEFORE taking any action that would violate your business rules.

 

[Kempes]

Thanks for the response Doc Man.

My db is vastly rich of many forms and controls so there is no way anyone other than admin can get into the back end tables/queries etc.

The question was posed more as a quick fix due to the size of my DB. There are many areas that will rely on the rules as stated above.

Thanks anyway, I guess I'll just have to code each area to perform the checks to disassociate related records.

Thanks again

 

[Len Boorman]

The way that I handle this situation is to
1) Have a delete field (Text Y or N)
2) on the Delete Display I only show those items that do not have related records so user cannot even attempt to delete iof there is a related record
3) I have Master User functions that warn strongly but allow delete of records with associations via the cascade delete on the relationship


Maybe so variant of these thoughts may be the way to go

L