Delete Query

[OliverWilton]

Hey, i have a problem in as much sense as i'm not sure how to go about it more than anything but it's definatly going to be a query.

I have a database with a main table called Products, then related tables coming off it. My forms are set up to read the drop down boxes from their specific tables(the related tables, not the products table) and the value is stored (bound) to the Products table.

The related tables obviously have values stored inside of them. Now i want to set up a query that can be run by a user so it deletes that record but not the record it's stored under

EXAMPLE:

Under Item Number 1.. the Buyer may be stored as Buyer #1. I want to Delete Buyer #1, but still keep every other field stored under Item Number 1. I tried creating a simple delete query against the Buyer (which works if they're not selected under any item number, but if they are then it causes this error)

"Microsoft Access Cannot Delete(1) Record Due to Key Violations"

I understand that this is because it's a buyer selected in my database, but i would like to know if there is a way around this.

I tried "Cascade Update Related Fields" and "Cascade Delete Related Fields" in the relationships, but that deletes the entire item.

Sorry for the long post, hope you understand what i mean as i'm bad at explaining! Any help appreciated, Thanks.

 

[boblarson]

Don't delete the item, but set it up so it is just inactivated and then becomes invisible.

 

[OliverWilton]

Could you perhaps talk me through the methods to take to do that please?

Thanks

 

[boblarson]

What you do is add a boolean field (titled something like Inactive) to your table where you have the buyers listed. Then, you set up your forms and such to only pull those items that have field Inactive = No. Then in your "delete" area you just set the field for that buyer to be yes instead of no.

Hopefully that will get you started.