If date statement for book loan

[rema96]

First of all hello and thank you for reading.

I'am making a database for a Library, the tables I have are:
Student - ID, Name, Class, Sex, Address
Books - ID_book, Name, Writer, ID_Genre
Loan - ID_Student, ID_Book, Take, Return
Genre - ID_Genre, Genre

So what I want is, if I loan a book and it is not returned Loan(return) = is null no one else can take it.

Exp:
User: 1 takes a book ID:2, 5 minutes later User: 52 tries to take book ID:2 but he cannot because it's taken by user 1.

Thanks in advance

[twinnyfo]

rema96,

First, I would caution you against using the reserved word "Name" as a Field name in your Tables (use StudentName and BookName), as these can sometimes throw Access into a hissy fit if you are not very careful with how you use those words.

Second, You have not provided anything for us to go off of. What have you tried so far? How are you using this data? If a book is currently checked out, how can anyone else access that book in the library (since it is physically gone)?

I could probably build my own Library database, but it would not suit your needs. Please show us the work you have accomplished so far, and we will be glad to troubleshoot any problem areas for you.

[rema96]

Thanks for the fast reply, I am not using name, I just putted it there for you guys to understand better.

Don't really get the question.
Here are my relationship

http://i.imgur.com/QGtp4Mi.png

[rema96]

What I tired to do is list all available books but I was only able to list the books that are in Loan table. But I want to list all the Book then check with the loan table and criteria for Loan is Return (Not Null)

[NeoPa]

When Twinnyfo suggests that you show us your work he's talking about posting your work in here rather than simply explaining what you have done in very general terms.

I'm sure if you read his post again carefully you'll understand what is required. If you have queries that aren't working as you'd like then post the SQL of what you have. If it is VBA code that is failing then post that. Here's a link to some more specific instructions should you need more help with this part - Before Posting (VBA or SQL) Code.

[jforbes]

I imagine you have an Action of some kind that creates the Loan Record. That is the Action that I would lock down.

So, if you fire that Action off from a Command Button on a Form that displays Book Records, you could add a routine to Enable or Disable the Command Button on the On Current Event of that Form based on the availability of the Book. Like:

Private Sub Form_Current()
    Me.Command6.Enabled = (DLookup("User_ID", "Loan", "Book_ID=" & Me!Book_ID & " AND Return IS NULL") > 0)
End Sub

But this is about as far as I can help without knowing more about you specific project.

[rema96]

Maybe I went with this the wrong way. You guys are really helpful but I'am really new to Access.

Here is maybe a thing you can help me, solves this complicate problem.

I have an Add record button and I want when it add's a record (for the loan) to change the Book.Status to 0

So I go to Loan Form

Select the user and the book, press Add Record it add's it to the Loan Table and in the Books table changes the status to 0

[zmbd]

rema96:
What you attempting to do is not a simple task - doable; however, IMHO, not one I would suggest that a first time developer should try from scratch.

First: Because I hate to see anyone re-invent the wheel if not absolutely needed: There are a few very good open source freeware library management system software programs available. In your favorite search engine use these terms freeware library management software look at the offerings there and see if anything will work for you.

Second: Do you have to have a history of the book's circulation? If not we can simplfy your life a tad.

It would also help to understand the scope of the project auch as: small home library, small public circulating library, etc...

We do try to limit one question per thread.


(for later reference, The first 20 pages are high level but ntl good info Thesis Delvmnt DBMS for Library... start pg43 (adobe 51))

[NeoPa]

Please refer back to posts #2 & #5.

Ambiguity can be a problem sometimes so let me make it very clear that any further posts that try to take this thread off the question, or even to progress but without responding to the request for what you have already, are very likely to be deleted.