i have a table called "loan_tbl" and when a person borrows a library book, the librarian enters the details into the form attatched to this table (loan_frm)
i need to come up with a way of making it so that when a loan is entered, if that book is already on loan, it cant be borrowed again until the book is returned (a date is entered in the "date returned" field).
all books have a book number and when the librarian enters the book number in the loan form, if that book is already on loan a message appears saying "sorry, book out"
queries or macros can be used, but i will work with code if i have to.
You could try this... In the BeforeUpdate event of the book number entry field, copy place this code:
If Not IsNull(DLookUp("[BookNumber]", "loan_tbl", "[BookNumber] = '" & Me.frmBookNum & "'") Then
Dim BkName As String
BkName = DLookUp("[BookName]", "loan_tbl", "[BookNumber] = '" & Me.frmBookNum & "'")
Msgbox "Sorry. The book named " & BkName & " (" & Me.frmBookNum & ") is allready out on loan."
Cancel = True
BookNumber is the name of the table field which holds the book number. loan_tbl is the table name. Me.frmBookNum is the book number entry field on your form. BookName is the name of the table field which holds the book name.
hey thanks for the code. i assume i paste that into a new module.
the only problem is that the top line comes up in red. what does this mean?
also how do i make the module run from a command button on the form?
when i paste the code above in the before update event of the book number entry field (in the properties) and when i enter a value in teh "book number" field, access displays a message saying it cant find some macro. i have attatched a screen shot of the message.
Sorry about that....I forgot a Bracket. Please modify the first line to read:
If Not IsNull(DLookUp("[BookNumber]", "loan_tbl", "[BookNumber] = '" & Me.frmBookNum & "'")) Then
if you want to place this code into a command button then simply create a commanc button on your form then place the code into the OnClick event (located in the properties window). You need to remove the statement Cancel = True though. It's not required in the button.
I personally think it would be better to place the code as is (with all the brackets of coarse) into the BeforeUpdate event of your form's book number entry TextBox.