deleting records and a one to one relationship.

[timmy041]

Hello all,

I have 3 tables:
1. customer
2. DVD collection
3. Borrow

The aim of this system is to create a home borrowing system.

The customer table has a one to many relationship with the borrow table, while the dvd collection has a one to one with borrow. I can create a new book with no problem, and even view these on a form. Here my proble arises.

I ahve another form to return a DVD. I view the records from the borrow table, and once found what i want there is a button which deletes the record from the borrow table. Sadly, when i press this button, not only does it delete the record in the borrow table, but it will also delete the record from the DVD collection table.

I have tried everything i can think of, and i don't want to change to change the design as i have found this mnethod very easy to generate reports and the like. Any help??

Thanks

EDIT: the button i used was from the wizard that comes up when you create a button.

 

[FoFa]

Sounds like cascade delete is on.
Why not have your Borrowed record use a returned date instead of deleting it?

 

[EdFred]

Make the relationship 1 to many from the DVD collection --> Borrow and just have a single primary key on the borrow table. Then you should be able to delete from the borrow table w/o it coming off the collection table.