Delete does not delete records in evey table

[LaughingBull]

I have a form to check out books data source is a query made up of 4 tables the code to prevent selecting a book already signed out is:

Private Sub BookName_ID_BeforeUpdate(Cancel As Integer)
If Forms!tblLibrary1!CheckedOut = True Then
MsgBox "This book is checked Out Select another"
End If
End Sub



and it works great. The problem is when I delete the record of a book that is checked out the record from the query and tables are deleted, but the yes/no field in the books table stays checked.

Even though the record is deleted when I select a book that was checked out in the deleted record the message says the book is checked out until I go and uncheck the field in the books table.

Any Ideas!

[June7]

Would require code to edit the CheckedOut field. This field is not really required at all. The CheckedOut value can be calculated. Whether a book is checked out can be determined by a query of tblLibrary or with DLookup() domain aggregate function.

[LaughingBull]

I am not sure how to create this code to only select books that have not been checked out not using the yes/No field. I tried the DLOOKUP using the yes/no field and it does not like that. It seems it has to do with the way I have the tables and the query set up but I am not sure. I watched this video https://www.youtube.com/watch?v=2Kq3jZ73PGQ and did the same and it says error.
=DLookUp("CheckOut","tblCheckOut","CheckOut=" & [BookName_ID])

[June7]

tblCheckOut is not a table in your image.

A query that lists books from tblBooks that are not in tblLibrary would be a Find Unmatched query - there is a query wizard for that.

Also:

SELECT * FROM tblBooks WHERE NOT ID IN (SELECT BookName_ID FROM tblLibrary);

So you don't want to keep history of check out/in?

[LaughingBull]

A book is checked out and when it is checked in I want the record deleted no history
I will check it out and let you know what happens

Thank you

[June7]

Then you can eliminate a table. Just have the CheckOutDate and CheckOutBy fields in tblBooks. When book is returned set those fields to Null. DueDate can be a calculated value.