detect if a record is stored in a 2nd table

[ZahleServ]

Hi again,

as i already mentioned in my 1st post i'm creating a small software for my bookstore, i've created the search system 10x to the help of many posts here. I've been trying around all day long to create a system that detect if a given book is already taken, if so i should get the msg "sorry book already taken choose another one" but it's not working. my books table has the following fields:

BookID
Title
Author
Edition
Price


and i have another table named takenbooks that stores the full name and the bookid of the one who got a book.

the code should lookup the BookID in the "takenbooks" if it's available => the book with this code is taken. if not => it's available ...

Hope u got my point

Thanks

 

[WayneRyan]

ZahleServ,

You can use the AfterUpdate event to do a
DLookUp. Something like:

If Not IsNull(DLookUp("[BookID]", "OtherTable", "[BookID] = " & Me.BookID)) Then
   MsgBox("That book is already out.")
End If

Wayne

 

[ZahleServ]

ZahleServ,

You can use the AfterUpdate event to do a
DLookUp. Something like:

If Not IsNull(DLookUp("[BookID]", "OtherTable", "[BookID] = " & Me.BookID)) Then
   MsgBox("That book is already out.")
End If

Wayne

negative i tried a similar procedure be4 posting and got a negative result.

 

[WayneRyan]

ZahleServ,

What did you get? An error? The wrong result?

Can you post your code?

Wayne

 

[ZahleServ]

the error is previous operation cancelled. i do believe my code is not clean...

here's a simple and clear explanation of what i really need:

1st i'm working with 2 tables:

Table 'Books' with the fields 'BookID, title, author, edition, price'

and

Table 'TakenBooks' with the fields 'BookID, name, date'

i have a form that display the results of the search within:

txtcode (the text box displaying the book id)
txttitle (the text box displaying the title)
txtauthor (displaying the author)
... and a command button "Buy this book" that open another form... What i need is BEFORE opening this form check if txtcode is already stored in 'TakenBooks' table in the field 'BookID'. if it's already stored => its already taken so the user should receive a msgbox instead of opening the 2nd form...

 

[rockman]

The code that Wayne provided should do the trick. Your example uses a command button so it should look something like this:

Private Sub cmdBuyBook_Click
     If Not IsNull(DLookUp("[BookID]", "TakenBooks", "[BookID] = " & txtcode.value)) Then
          MsgBox("That book is already out.")
     Else
          'Open other form and add any further code here...
     End if
End Sub

Also remember, if the BookID is a string (not a number) then the criteria in the DLookUp should have quotes around it.

HTH,
Jeff

 

[ZahleServ]

it turn me crazy , the good side is that i didn't get any error with the code but the bad side is that it opens the 2nd form even if the code is available in the table. i use for the codes a combination of chars and numbers (eg: AB34)

 

[ZahleServ]

it's finally working wow! 10x u guys for the kind help

 

[rockman]

Glad to hear its working. Good luck with the rest of your project.

-Jeff