Information from a form using a query

[Guest]

I am not sure if this is the correct place to put this question. I am
creating a library application. When someone checks out a book, I want to
check the database first to see if that book is in or out. I have the book
num as a text box and the in/out is in a table. Is there any way to have a
query return something in the VBA so that I can do a if ... then clause. I
normally run queries using DoCmd.openquery but it won't work in this case.
Thanks.

 

[Guest]

Your best bet here, rather than a text box, is to use a combo to search for
the book number. Keep the text box, but to avoid confusion, make it invisible
with no tab stop.
Then, in the After Update event of the Combo:

Dim rst as Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[BookNum] = " & Me.cboBook
If rst.NoMatch Then
MsgBox "Book Not Found"
Else
Me.Bookmark = rst.Bookmark
Me.txtBook = Me.cboBook
Me.cboBook = Null
End If
Set rst = Nothing
If Me.txtStatus = "out" Then
MsgBox "This Book is already Checked Out"
End If