Thread: Problem Returning a Book in a Library System

Okay so I've been here before and some of you may remember me. It has gotten to the stage now where my library is complete and I only have one tiny problem that I can't for the life of me figure out how to fix, so heres hoping you can:

Okay so the problem occurs when a user wishes to return a book to the library. To return a book the user simply chooses the borrow ID (the primary key) from the drop down in the form and selects return. The script on the return button adds one to the stock of the book, marks the borrow as returned, sets the returned date as the current date, and then compares the due date of the borrowed book with the returned date to calculate if a fee is due. If the book is late the due date is subtracted from the return date to find out how many days i has been late for. An overdue fee of £1 is added to the members account for every day the book is late and if the fee is voer £50 they are banned.

Here is the code:

Code:

Private Sub Borrow_IDSelect_Change()
'When user changes the borrow ID field
'Open the form again showing the record with this ID
DoCmd.OpenForm "BorrowReturn", , , "[Borrow_ID] = " & Me.Borrow_IDSelect
End Sub


Private Sub Confirm_Click()
'When the confirm button is clickec
On Error GoTo Err_Confirm_Click

Dim diff As Variant

'Add one to the number of stock for this book
Let Me!Stock = Me!Stock + 1
    
'Set the return date to the current date
Let Me!BorrowReturnedDate = Date

'Mark the borrow as returned
Let Me!BorrowStatus = True

'Save the record
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Check if the book is overdue
If Me!BorrowReturnedDate > Me!Due_Date Then
'Calculate how many days the book is overdue
Let diff = Me!BorrowReturnedDate - Me!Due_Date
'Add the over due fee for this borrow to the members ofverall fee
Let Me!Overdue_Fee = Me!Overdue_Fee + diff

'Check to see if the members fee is over £50
If Me!Overdue_Fee > 50 Then
'Ban the member
Let Me!Member_Active = False
End If
End If

'Save the record
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Close the form
    DoCmd.Close
    
'Open the return successfull form
    DoCmd.OpenForm "ReturnSuccess"
Exit_Confirm_Click:
    Exit Sub

Err_Confirm_Click:
    MsgBox Err.Description
    Resume Exit_Confirm_Click
    
End Sub

The problem occurs when a user tries to return a book late. What happens is that the Return Date is added, the stock is added, and the borrow is marked as returned but the overdue fee is not calculated nor added to the member and I get the following error:

The expression entered has a field, control, or property name that Hunners O Books can't Find

Hunners O Books is my library name.

I can upload the latetst copy of my library if it helps, just ask.

Thanks to anybody who can help.

What line is highlighted by the Debugger? You may wish to move to the newer RunCommand syntax from the now outdated DoMenuItem syntax.

The debugger doesn't show any problems as far as I know, I just get that error.

I tried using the DateDiff function to subtract the dates but I couldn't get that to work eaither. I am fairly new to access though, this is my first project I've had to work on.

In the property sheet for the Borrow_IDSelect control, what displays in the Change event row? If it is not [Event Procedure] then you need to press the "..." button so it will sync with the code you have.

It currently says [Event Procedure] so it's not that. Any other suggestions? A teacher of mine looked at it and couldn't find a fault in the coding or the relationships, and I have looked at this for hours with no solution. I have a feeling there is a probelm with the IF statement but I'm not sure.

I come from a procedural background with my programming experience being in TrueBasic, I am completely new to Access so I don't have the best of knowledge on this subject.

Originally Posted by Xavier

I tried what you said and nothing happened, it just made a ping noise. Would it help if I uploaded a copy of my database file?

Yes it would at this point. Thanks.

Compact and Repair first and then zip up what is left. If it is still too big to post then try using one of the free file hosting services and post the URL.

My Database

Theres the links, thanks for yopur help so far, hopefully we can get this sorted becuase it has been buggging me for almost a week now. I bet it will turn out to be something simple and stupid.