Is it possible for a message box to display a single record from a QBA query from a form?

[Emma G]

Hello,

I am currently constructing a library database. I have a 'Check in' form where the user enters the 'barcode number' of a book into the form and clicks a 'submit' button which runs a query that uses that data to locate and delete the record from the 'On Loan' table. That all works fine. I have currently designed it so that once the user clicks the 'submit' button the query will run and a message box will appear displaying the same message 'Check in was successful'. This chain of events was created using macros.

My question is...is there a way of getting the message box to display the patron that Checked In the book in the first place instead i.e. 'Thank you [patron name] your book has been successfully checked in'. The name of the patron would be in the 'On Loan' table under the same record that the other query I mentioned would use. It would obviously need to display a different name each time. I imagine I would need to use a query to do this. Is there away this could be achieved? I feel that it would greatly improve the user interface of my library database if so.

Many thanks,



Emma

[NightWalker]

Emma,

You can do that. First I would convert your macros to code. but you can add this to your code and it will give you the message you are wanting

MsgBox "Thank you " & (UserName) & vbCrLf & "Book successfully returned."

UserName being where the patrons name is stored if it is a txt box

[Micron]

If you delete the record first, the name is gone. If you show the message then the delete fails for any reason, the book is still OnLoan even though it has been checked in. Consider flagging the record instead of deleting. Not sure if you have another means of tracking loans, but this would also allow you to report on patron loan frequency, loan counts on any given book, or anything else you can think of.
EDIT
A query or DLookup can do what you want. You've posted in the Forms part of the form, so I imagine you have one to work from as was assumed. However, if you have more than one copy of a book, how are you tying the returned copy to Mr. Smith instead of anyone else who has borrowed one?

[Emma G]

Emma,

You can do that. First I would convert your macros to code. but you can add this to your code and it will give you the message you are wanting

MsgBox "Thank you " & (UserName) & vbCrLf & "Book successfully returned."

UserName being where the patrons name is stored if it is a txt box

Thank you NightWalker. I will give that a go!

[Emma G]

If you delete the record first, the name is gone. If you show the message then the delete fails for any reason, the book is still OnLoan even though it has been checked in. Consider flagging the record instead of deleting. Not sure if you have another means of tracking loans, but this would also allow you to report on patron loan frequency, loan counts on any given book, or anything else you can think of.
EDIT
A query or DLookup can do what you want. You've posted in the Forms part of the form, so I imagine you have one to work from as was assumed. However, if you have more than one copy of a book, how are you tying the returned copy to Mr. Smith instead of anyone else who has borrowed one?

Currently I am appending the record to a 'Loan History' table before deleting it. In regards to having more than one copy of a book I was planning on giving each book an individual barcode sticker, unless you can think of an easier way?

Please coud you explain how to flag a record.

Many thanks,

Emma

[Micron]

Individual bar code would be a good method. To flag a record, you need a field whose value you can set appropriately. In your case, a Yes/No field that you set to True when the book is returned. Or a text field that you set to "True" or any appropriate text value. Or a numeric field that you set to 0 or 1 (least preferred for your situation, IMO). At this point, I'm not seeing how you grab the correct name if you don't have the bar code yet.
Another afterthought:
Don't mean to be critical, but if you're moving records around to accomplish this history, it sounds like you have db design issues. It's not a good idea to move records like this. You can get your history and status by querying the OnLoan table, especially if it has a returned flag. You say you have the patron name in the OnLoan table also? That would be poor design choice, I'm afraid.

[Emma G]

Individual bar code would be a good method. To flag a record, you need a field whose value you can set appropriately. In your case, a Yes/No field that you set to True when the book is returned. Or a text field that you set to "True" or any appropriate text value. Or a numeric field that you set to 0 or 1 (least preferred for your situation, IMO). At this point, I'm not seeing how you grab the correct name if you don't have the bar code yet.
Another afterthought:
Don't mean to be critical, but if you're moving records around to accomplish this history, it sounds like you have db design issues. It's not a good idea to move records like this. You can get your history and status by querying the OnLoan table, especially if it has a returned flag. You say you have the patron name in the OnLoan table also? That would be poor design choice, I'm afraid.

I have test barcode values that I am using. Thank you for explaining how to flag a record. I understand how you mean and how to change the yes/no field but im not sure how you could use this to track how many loans a book has had etc. as you mentioned in your earlier post?

[Micron]

You wouldn't. For that you would count the records per book. The flag was to tell you it had been returned without moving the record. You could also count how many were in versus out. Still thinking you have design issues though.

[Emma G]

You wouldn't. For that you would count the records per book. The flag was to tell you it had been returned without moving the record. You could also count how many were in versus out. Still thinking you have design issues though.

By design issues are you referring the append and delete query sequence and think the yes/no box change would be better?

[Micron]

No, I get the impression you have patron names in a loan table because you were going to get the name from it for your message box. Plus, you are/were moving records around. Kinda raises red flags for me. Suggest you go here
http://www.databaseanswers.org/data_models/
and see if any of it makes sense and resembles what you are doing. There is a db model section for a library.
If it raises any flags for you, maybe start a new thread pertaining to db design since that would not be related to your original post.

[Emma G]

Thank you Micron for all of your advice and for the link.

Have a great weekend.

Emma

[Emma G]

Emma,

You can do that. First I would convert your macros to code. but you can add this to your code and it will give you the message you are wanting

MsgBox "Thank you " & (UserName) & vbCrLf & "Book successfully returned."

UserName being where the patrons name is stored if it is a txt box

Sorry NightWalker, could I ask a little bit of further advice. My ´Check In´ forms runs by a patron typing/scanning the barcode number of the book into the ´Barcode Number´ text box. The forms then uses this information in the query ´qryCheckIn´ that locates the record including the ´Forename´ and ´Surname´ from the ´On Loan´ table and then appends it to a table called ´Loan History´ using the ´qryCheckInAppend´. There is no actual text box on the form that contains the patrons name.

I have converted the Macros in this form to VB as you suggested. However, as I am new to coding I am unsure how to refer to the ´Forename´ and ´Surname´ fields in that line of code you supplied in order for it to be used in the message box to display the name of the patron who borrowed that particular book. How would I do that?

Many thanks.

Emma

[NightWalker]

Emma,

I will try to get back to you with in the next day or two. If someone else gets to it first that would be great but I will have limited access to my computer for the next couple days.

I will reply as soon as I can.

Sorry,
Walker

[pbaldy]

It might help to see your code, but if it's only in the query and that query returns a single record, then you could do:

Dim strUserName as String
strUserName = Dlookup("Forename", "qryCheckIn")
MsgBox "Thank you " & strUserName & vbCrLf & "Book successfully returned."

[ssanfu]

Without seeing the code or the SQL of the queries, there are a couple of ways to go:

In the check-in code, you would open a recordset on "qryCheckIn" (if the query is a select query) and

A) set two variables to the "Forename" and "Surname" value

sFName = r!Forename
sSName = r!Surname
sMsg = "Thank you " & sFName & " " & sSName & vbCrLf & "Book successfully returned."
Msgbox sMsg

OR

B) by construct a string something like

sMsg = "Thank you " & r!Forename & " " & r!Surname & vbCrLf & "Book successfully returned."
Msgbox sMsg

It would really help if you post the code and the SQL of the two queries (qryCheckIn & qryCheckInAppend)

BTW, I would have to agree with Micron about your table structure probably needs work.