Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: Library scenario - I'm at my wits end

    I'm really struggling with this and if anyone could help me I'd be very grateful.

    I've made a simple database of a library system - the usual tables - books, members and Loans. In the loans table is Loan ID, Book number, date borrowed, date due back, date returned.

    I'm trying to work out a system that will prevent users from borrowing a book thats already out - in other words, where the date returned in the loans field is null.

    So I want a system where a parameter query asks [which book?], the user inputs the bookID, this is then checked to see if it appears in the loans table AND it's date returned is null.

    If this situation does occur, it should open the Enter New Loan form - if the book IS already out, a message box could appear saying this.

    I have tried everywhich way I can to this, but don't know (a) how to pass the parameter query to something else for it to do the checking, and (b) how to check whether this entry appears in the Loans table and whether its relevent cell for date returned is null.

    Amy help would be very gratefully received.

    Cheers

    Garfs

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Use a dcount function to check for null return dates for a given book id.

    If DCount("book_id", "loans", "date_returned IS NULL AND book_id = " & bookid) Then
    'do your open form stuff
    Else
    msgbox "Book already checked out"
    End If

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    Wow - incredibly fast reply - thanks so much. I assume that this code somehow goes into the VBA script thing? Umm - I get confused with all the modules etc..!

    Or have I got that wrong? Where would I actually put that function?

    and what would the 'open form stuff look like?

    Cheers bud

    Garfs

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't really know what your form looks like or how your user selects a book to check out.. I'm guessing there's a button they press somewhere after they have one picked out. You could put that code in the OnClick event for the button. Note that the "bookid" in the last bit of that statement should be whatever the name of the textbox is that has the book id on your form.

    OPening a form uses the DoCmd.Open acForm, "yourformname" method.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •