Cascading Combo in an embedded continous subform

[bob fitz]

Hello Bob

I am happy to report that I have finally got the database working the way I wanted. Adding another level of item classification certainly filters the records more effectively but led to a major headache trying to get the thing to work. Eventually, I opted for a complete re-build. which forced me to study your code closely in order to modify it to suit the new design. Getting your code to work was the key. I also discovered some flaws in my logic which would definitely have caused problems further down the line. So once again thanks for all your help. I could not have got this far without you.

Rob

I have to say that I don't feel I have been able to help as much as I would have liked with the latest problem but none the less I thank you for your kind words and wish you well with your project

 

[Robbyp2001]

Dear Bob
I hope you don't think this is too impertinent of me but you may be the man to help with this database problem. A different database! Hopefully you won't mind me asking.
Here's the scenario. I have constructed a library system. It works well and the users are happy with it, but recently we discovered a problem with books not being returned and other books being borrowed by the sme student.

The borrowing process works thus: When the user selects a book, on the [MainSearchScreen] the [StudentID] is entered by scanning the barcode on their library ticket and the BookID and StudentID are created in the [BorrowTbl]. These details remain there until the book is returned and the borrow details deleted.

However there is no way to ascertain of the student already has a book on loan at the point of borrowing therefore, in theory at least, he or she can borrow an unlimited number of books without returning any of them, as long as no one recorgnises them! They will flag up as overdue at some point, but by then it's too late. The librarian is pulling her hair out as the younger kids are very forgetful when it comes to returning books and consequently they are not avaiable for any other user.

What I would like to know is, is it possible to run some code, at the point of entering the [MainSearchScreen]![StudentID] so that it scans the records in the [BorrowTbl] and flags up [StudentID]>1? If yes, then the process should be halted and a message appears to advise that the stuent already has at least one other book currently on loan, with if possible, the number of books currently on loan, and the option to override this block if needed.

So, Msg "This User Currently Has X number of books On-Loan"
"Cancel This Request Y or N?"
If "Yes" Request to borrow is denied and nothing can progress
If "No" allow the request to progress as normal.
If the user does nt have an other books on loan, the request proceeds as normal with no message.

Anyway Bob, if you can help, here are the instructions for entering a new record:

1. On the opening Switchboard, select 'Library Functions'
2. Select 'English Language Books'
3. To the right of the Main Screen in each Book record, there is a box ID No. Enter the number 1339 in any of the corresponding books and press enter. The book will flag up On-Loan. it is now in the [BorrowTbl].
4. When the book is returned, the user clicks the Book Return button and the record is deleted from the [BorrowTbl]

I have attached a copy of the system in the hope that you might have the solution

Many thanks

Rob

 

[bob fitz]

Hi Rob

Should be fairly simple to fulfill your requirement and I would be happy to have a look at it but you would need to post a copy of the db as an .accdb file or as an ordinary .zip file as I am unable to open your .zipx file.

 

[Robbyp2001]

How about this one Bob.

 

[bob fitz]

How about this one Bob.

Yes, that's better. At least I can get this open but may not have time to look at it till Tuesday now

 

[Robbyp2001]

No problem Bob. Any help you can give will be gratefully received.

Rob

 

[bob fitz]

Hi Rob

Quick attempt before I go to bed now. Paste the following code into the Before Update event of the text box named StudentID

Dim v As Variant
    v = DCount("StudentID", "borrowtbl", "StudentID =" & Nz(Me.ActiveControl, 0))
    If v > 0 Then
        If MsgBox("Student has already borrowed " & v & " books. Cancel This Request Yes or No?", vbYesNo, "Allow Request?") = vbYes Then
            MsgBox "Request Denied"
            Cancel = True
            Me.Undo
        End If
    End If

 

[Robbyp2001]

Bob, you are amazing! I'll check it out properly tomorrow, but it looks fantastic.

Thank you

Rob

 

[Robbyp2001]

Hello Bob. The library database has been running for the past couple of days, using your new code, and so far the librarian is delighted. I think she enjoys the look of horror on their little faces once they discover they've been rumbled! Credit where credit's due, I told her that it was your code that was running. She passes on her thanks and says she'll have your baby anytime! I made the last bit up. She did say thank you very much though.

Thanks from me too.

Rob

 

[bob fitz]

Hi Rob

Glad to hear that my code fulfills your needs . Sorry to hear that I will not be able to fulfill the librarian's needs .
Your thanks are appreciated. You may like to consider clicking the thanks button (bottom right) or adding to my reputation by clicking the scales (bottom left).

Just a word on etiquette.
Posting a supplementary question on the original question in the same thread is fine. However, if you have a question on a new question on a new subject/problem then that should really be asked in a new thread. If you particularly want someone to look at something you can always send them a message with a link to the thread.

 

[Robbyp2001]

Buttons clicked and etiquette faux pat duly noted. Thanks Bob.