Deleting an erroneous record automatically

[Robbyp2001]

Hello folks, I have a question for the boffins on here. I have a school library system set up and one of its functions is to record each student's library activity (for awards at the end of the year). The function works well, however I've thought of a scenario which could well occur at some point. If the librarian checks out a book and mistakenly adds the wrong student name, the details will be logged in a table called [BookStudentTbl]. He or she can then rectify this error by changing the student name on the main form. However when applying the correct name, both records now appear in [BookStudentTbl]. One of these records (the earlier one) is of course, wrong. My question is: How can I automatically get the system to check for this error and delete the erroneous record?

My thoughts are: somehow check for the same book (bookID), issued twice on the same day and delete the first of these, as this will be the erroneous one. The BSID autonumber will show which of these was created first, but how do I delete this? Am I approaching this in the wrong way?

Any advice will be gratefully received as I am stumped by this!

[BookStudentTbl] has the following fields

BSID AutoNumber
StudentID Number (keyfield) Unique
BookID Number (keyfield) Unique
DateOut Date/Time (keyfield)
DateIn Date/Time
Time Date/Time
TimeIn Date/Time

Note: The combination of key fields is to reject a record where a student may borrow a book, return it on the same day and borrow it again, thus recording two books borrowed instead of one.

 

[static]

Instead of in/out dates (I'm not sure why you have 4?), I would use one field for date/time and another for status like 'in' or 'out'.

Then you just get the latest date and check the book's booking status.

A book can be in or out, not both. So the process for checking a book out on the main form might be...

Select book
Select student
Check status of book
1) If status is 'out' either
a) the book wasn't booked back in in the database - add a dummy booked in record and goto 2. or
b) you want to change the name. Update the existing record.

2)If status is 'in' add a new record as normal.

Then, since your code is managing the data, you can put the PK on the autonumber.

 

[moke123]

He or she can then rectify this error by changing the student name on the main form. However when applying the correct name, both records now appear in [BookStudentTbl].

How is the record being written to the bookstudenttbl? Is the main form creating a new record or is it filtered to the record that needs to be corrected?

to check if a book is checked out twice in one day you could use a dcount with the bookid and date as criteria. If you use now() for a timestamp be aware that you would need to use only the date portion, not the time, in your dcount.

 

[Robbyp2001]

Instead of in/out dates (I'm not sure why you have 4?), I would use one field for date/time and another for status like 'in' or 'out'.

Then you just get the latest date and check the book's booking status.

A book can be in or out, not both. So the process for checking a book out on the main form might be...

Select book
Select student
Check status of book
1) If status is 'out' either
a) the book wasn't booked back in in the database - add a dummy booked in record and goto 2. or
b) you want to change the name. Update the existing record.

2)If status is 'in' add a new record as normal.

Then, since your code is managing the data, you can put the PK on the autonumber.

Many thanks Static.

Perhaps I wasn't clear about my processes. Here's what happens:

1. The librarian checks the book out by selecting the student from a list. This is applied to [BookTitleTbl]
2. The book record is then flagged as 'On Loan' in the book title table.
3. The Date out is set to Date() in [BookTitleTbl]
4. The Time is set to Time() the [BookTitleTbl]

This information is then stored in a different table - [BookStudentTbl]. This is where a permanent record of library activity is held.

The [BookStudentTbl] now looks like this:
BSID StudentID BookID DateOut ......DateIN ...TimeOut TimeIn
5......924..........4046....09-12-2016 ................08:20
4......1210 .......4046.....09-12-2016 ...............08:20

So, the librarian accidently allocated the book to student 924, then upon realising the mistake, changed the student to 1210. However both records remain in the [BookStudentTbl].

The [DateIn] and [TimeIn], are updated date() and time(), when the book is returned. So, ready for re-allocation.

In the above case, the book was simply reallocated to a different student without being returned to the library first, therefore there is no DateIn or TimeIn in either record.

When the book is returned, the procedure updates [BookTitleTbl]![Status] from 'On Loan' to 'Available' and the [BookTitleTbl]![dateOut] and [BookTitleTbl]![Time] to null, ready for re-allocation. No problems there.

I somehow have to delete the erroneous record from the [BookStudentTbl] as soon as the new StudentID is applied.

Whew! That was a bit long-winded, apologies.

As you can see, the table now has 2 StudentIDs but identical BookID, DateOut and TimeOut (if the error is noticed immediately), but the BSID is different. I need to delete the lower of the two BSID values (in the above case, 4) so leaving only the correct record (5).

 

[Robbyp2001]

How is the record being written to the bookstudenttbl? Is the main form creating a new record or is it filtered to the record that needs to be corrected?

to check if a book is checked out twice in one day you could use a dcount with the bookid and date as criteria. If you use now() for a timestamp be aware that you would need to use only the date portion, not the time, in your dcount.

Many thanks Mole

The main form is based on a table containing all the Book Details. Each book is displayed in the continuous form and here is a combo box for selecting the student. After update of the combo box the underlying table [BookTitleTbl]![StudentID] is populated by StudentID number and [BookTitleTbl]![dateOut] is populated by date().

This information is then appended to a different table [BookStudentTbl] where a permanent record of StudentID, BookID, DateOut and DateIN are kept.

When the Book Return procedure is invoked the [BookTitleTbl]![StudentID] and [BookTitleTbl]![dateOut] are reset to null.

That part works however it is in the [BookStudentTbl] that potential problems exist. Where there is a record of Identical BookID and DateOut I need to delete the earlier record which is identifiable by the BSID (autonumber). The erroneous record will always be the lower of the two.

 

[Minty]

I actually think you would be better off having a "Oops" button that opened the record concerned and let them update it to the correct Student? No need to try and identify when what or how the erroneous record is.

 

[gemma-the-husky]

are you storing the actual student name in the lending table?

if so, you would be better storing an ID corresponding to the student name, and then storing the student ID in the lending table.

That way you can correct the name without causing problems.

It sounds like you are just trying to correct an incorrectly recorded name - rather than cancel a book allocation to the wrong student.

 

[Robbyp2001]

Thanks Gemma and Minty

I am indeed trying allow a student name to be replaced if erroneously entered, or for the offending record to be deleted. The student ID is stored in the [BookTitleTbl], until the book is returned. Upon its return, it is then updated to null to make way for the next borrower.

The same details are appended to the Student Library record table([BookStudentTbl]) and stored there permanently.

When the error is discovered, the librarian will change the name from the same combo box that was used in the first place. So now the book is assigned to the correct student. But in doing this, the system once again appends the details to the [BookStudentTbl], So now it appears that the book has been borrowed twice by two different students on the same day.

The current procedure is thus:

Assign Student to the Book Title-Combobox After Update

DoCmd.OpenQuery "UpdateBorrowDates" This updates the [DateOut] to date() in [BookTitleTbl] and updates the [StudentID] in [BookTitleTbl]

DoCmd.OpenQuery "AppendStudentBorrowRecord" This sends [StudentID], [bookID] and [DateOut] combination record to Student Library Record Table [BookStudentTbl]

This is what I need to do (I think)
If this [BookStudentTbl]![bookID] and [BookStudentTbl]![DateOut] combination already exists...

Then Update [BookStudentTbl]![StudentID] to the form [MainScreen]![combobox]...

Or delete the erroneous record.

I had thought about an Oops button, but I would prefer not to create another procedure. The Librarians are not system confident.

 

[gemma-the-husky]

As far as I am concerned the right solution is to store a UserID, not a user name.

I am also very reluctant to allow combo boxes to be other than "limit to list" as otherwise junk WILL get entered in the names list. If you set them to "limit to list", then have a "not in list" event you can manage the "new users" in a much more secure way.

 

[gemma-the-husky]

in passing, this must be an error in your logic.

Given a "lending record", you should be able to change the "borrower name/Borrower ID" without it creating a new lending record.

it might be that by creating a new name in the users list, you are adding a new lending record that isn't required for a change, as opposed to a new lend.

When the error is discovered, the librarian will change the name from the same combo box that was used in the first place. So now the book is assigned to the correct student. But in doing this, the system once again appends the details to the [BookStudentTbl], So now it appears that the book has been borrowed twice by two different students on the same day.

 

[jdraw]

I agree with Dave, userId/borrowerId --not name in related tables.

Here is a link to a free data model (library system) that may be useful to you. Note that it is generic and may have features that are not within your scope. It is meant as a reference to typical set up of a library database. Your requirements may differ.

As for assigning the wrong borrower to a loaned item, one approach is to ask for confirmation before accepting the selection.
You know--- You have chosen Borrower 6 John Doe - Is that correct? Yes--Accept the borrower in your code. No - prompt for the Borrower, then do the confirmation loop.

Good luck with your project.

 

[Robbyp2001]

in passing, this must be an error in your logic.

Given a "lending record", you should be able to change the "borrower name/Borrower ID" without it creating a new lending record.

it might be that by creating a new name in the users list, you are adding a new lending record that isn't required for a change, as opposed to a new lend.

I think I understand what you're saying. When the record of borrowing is created the student ID is inserted into the book title table and this indeed can be changed (I think this is what you were getting at). But the record is also appended to the [BookStudentTbl] at the same time. Not a new title or a new borrower but simply a record of that particular borrowing. That same student will build up many records of borrowing throughout the year. This is why the details of the borrowing are appended to a different table.

In order to simplify the procedure, the new borrowing record is created when the librarian selects the student from a combobox. By selecting a different student name because the first one was wrong, means that the procedure is repeated and a whole new record is appended to the [BookStudentTbl]. This is where the problem lies.

 

[jdraw]

When the record of borrowing is created the student ID is inserted into the book title table

Hmmm??? BookTable has nothing to do with borrower. BookTable is about Books. BorrowerTable is about Borrowers. You need a "UserBorrowsBook/Loan" table to identify who borrowed which Book(s), when and when it should be returned. These are facts about the "Loan".

Borrower--->UserBorrowsBook<------Books

 

[The_Doc_Man]

I think having a maintenance form to allow correction of an erroneous borrower entry is the best way to maintain this. If done based on JDraw's suggestion, this would be an easy form to build (a wizard could do the heavy lifting for the combo boxes impled.)

 

[gemma-the-husky]

But the record is also appended to the [BookStudentTbl] at the same time. Not a new title or a new borrower but simply a record of that particular borrowing. That same student will build up many records of borrowing throughout the year. This is why the details of the borrowing are appended to a different table.

so this is the "loan" table we have all referred to.

no doubt it contains the book details AND the student details.

What you need to do is either
a) delete the record from this table, and add a new one OR
b) simply amend the student details in this table, to reflect the erroneous data.,

 

[Robbyp2001]

Thanks to everyone who replied. The problem can be resolved if I modify the database structure. There are several fields that logically do not belong in the tables that they are in, as pointed out by some of you. See what happens when you don't plan well beforehand!!!!

At least it can work in the meantime.

Thanks again folks.

 

[jdraw]

Glad you have a path to a solution.
You will find that getting your tables and relationships set up to match your requirements, and testing with pencil and paper (and repeat as necessary), will streamline development. Too many times we see things like-- yes I know my tables aren't right, but I've got too much time invested to change anything.

Take the time, it will be quicker and better.

Here's an older tutorial from RogersAccessLibrary. If you work through it, you'll see/experience a procedure that can be used when designing any database.

Good luck.

 

[gemma-the-husky]

good luck with your project.

The underlying point, as you mention, is that if you get the structure right, then most coding issues are relatively straightforward.