Find record matching 3 fields

[Jim L]

I've designed a library database for my church. We will be scanning in a
patron barcode and a book barcode.

When people return books they will scan in the book and their library
card. I want to use VBA code with the onClick peoperties of a button to
locate a record matching the bookID field and the Patron field and a
blank returndate field, then update the return date.

It starts out ok:

BookScan = InputBox("Scan in the bar code on the book you are Returning:")
PatronScan = InputBox("Scan in your Library ID tag")

Here's where I'm stuck...
I'm not sure how tell Access to search for the record and update the
date field. The query below works, but I don't know how to convert it
into VBA.

UPDATE tblLoan SET tblLoan.ReturnDate = Date()
WHERE (((tblLoan.ReturnDate) Is Null) AND ((tblLoan.DeweyID)=[Enter book
ID]) AND ((tblLoan.PersonID)=[Enter Donor ID]));

If the answer is obvious to you, please clue me in. Thanks in advance!

Jim L

 

[Marshall Barton]

I've designed a library database for my church. We will be scanning in a
patron barcode and a book barcode.

When people return books they will scan in the book and their library
card. I want to use VBA code with the onClick peoperties of a button to
locate a record matching the bookID field and the Patron field and a
blank returndate field, then update the return date.

It starts out ok:

BookScan = InputBox("Scan in the bar code on the book you are Returning:")
PatronScan = InputBox("Scan in your Library ID tag")

Here's where I'm stuck...
I'm not sure how tell Access to search for the record and update the
date field. The query below works, but I don't know how to convert it
into VBA.

UPDATE tblLoan SET tblLoan.ReturnDate = Date()
WHERE (((tblLoan.ReturnDate) Is Null) AND ((tblLoan.DeweyID)=[Enter book
ID]) AND ((tblLoan.PersonID)=[Enter Donor ID]));

Here's a sketch of the kind of code you can start with:

strSQL = "UPDATE tblLoan SET tblLoan.ReturnDate = Date() " _
& " WHERE (((tblLoan.ReturnDate) Is Null) " _
& " AND tblLoan.DeweyID=" & BookScan _
& " AND tblLoan.PersonID=" & PatronScan

Set db = CurrentDb()
db.Execute strSQL
If db.RecordsAffected <> 1 Then
MsgBox "Whoops!"
End If
Set db = Nothing

 

[Jim L]

Marsh,

Thank you very much for your assistance! I had to adjust the code more
because I failed to mention that the DeweyID field was alpha-numeric
text. It took a while for me to figure out I needed quadruple quotes
where double quotes were required in the statement.

Thanks for sharing your knowledge with me. It helped greatly.

--Jim L.