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

[Emma G]

Thank you both for your replies. Hope I have done this right but here is the SQL code for the ´qryCheckIn´that locates the record based on barcode entered in the form ´frm CheckIn´

SELECT tblLoan.[Barcode Number], tblLoan.[Borrower ID]
FROM tblLoan
WHERE (((tblLoan.[Barcode Number])=[Forms]![frmCheckIn]![Text0])) OR ((([Forms]![frmCheckIn]![Text0]) Is Null));

Here is also the code for the ´qryCheckInAppend´ that append the record to ´tblLoanHistory´. (A delete query is then run to remove it from the ´tblOnLoan´.

INSERT INTO tblLoanHistory ( [Barcode Number], Title, Author, [Borrower ID], Forename, Surname, Class, [Date Returned], [AR Interest Level], [AR BL Colour] )
SELECT tblLoan.[Barcode Number], tblLoan.Title, tblLoan.Author, tblLoan.[Borrower ID], tblLoan.Forename, tblLoan.Surname, tblLoan.Class, Date() AS Expr1, tblLoan.[AR Interest Level], tblLoan.[AR BL Colour]
FROM tblLoan


WHERE (((tblLoan.[Barcode Number])=[Forms]![frmCheckIn]![BarcodeCI]));

If it helps, here also is the VB code that runs when the Patron clicks the Command button to run the querys.

'------------------------------------------------------------
' Command12_Click
'
'------------------------------------------------------------
Private Sub Command12_Click()
On Error GoTo Command12_Click_Err


' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
' <UserInterfaceMacro Event="OnLoad" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="MaximizeWindow"/></Statements></UserInterfaceMacro>
DoCmd.OpenQuery "qryCheckInAppend", acViewNormal, acEdit
DoCmd.OpenQuery "AvailableYesUpdate", acViewNormal, acEdit
DoCmd.OpenQuery "qryCheckInDelete", acViewNormal, acEdit
Beep
MsgBox "Thank you " & vbCrLf & "Book successfully returned."
DoCmd.Close acForm, "frmCheckIn"
DoCmd.OpenForm "frmCheckIn", acNormal, "", "", , acNormal




Command12_Click_Exit:
Exit Sub


Command12_Click_Err:
MsgBox Error$
Resume Command12_Click_Exit


End Sub

The line in bold is where I have started to enter the code that was initially suggested by NightWalker.

Hope this information helps. Thank you again for your replies. I am quite new to databases and even newer to coding so your advice is greatly appreciated.

Many thanks,

Emma

[pbaldy]

You can try to add the bits I mentioned. The other uses a recordset, which wasn't shown.

[Micron]

Wondering about the check in process and if it's value is mostly an exercise in figuring out how to do something in Access.
- Are you not scanning a library card first? If so, the name would be known at the start?
- If I return 5 books, is someone going to have to deal with this prompt 5 times?
- If I borrow the book but my wife returns it, the message addresses her as me?
I agree with ssanfu that more needs to be shown, but we might be dealing with macros. The solution probably lies in getting the values from a query or DLookups at the beginning of the process, then checking in all books, then displaying the message, albeit possibly to the wrong person.

[ssanfu]

I've been stumped on what to write. I think there are several issues that need/should be addressed - one of which is the table structures.
By this I mean you have tables "tblBorrower" (I'm guessing), "tblLoan" and "tblLoanHistory". In each table there appears to be fields for "[Borrower ID]", "Forename" and "Surname". The same data is being stored in 3 different tables! It is unnecessary - the fields "Forename" and "Surname" should ONLY be in the borrower table.

Not sure what query "qryCheckIn" is used for......

I would not be deleting and appending records - I would use the "flag" approach that Micron suggested.


As for the code for the message box, I came up with a blend of my suggestion and Paul's.
I don't know if [Forms]![frmCheckIn]![BarcodeCI] is TEXT of a NUMBER - I guessed a number.
Try this in a COPY of your dB:

'------------------------------------------------------------
' Command12_Click
'
'------------------------------------------------------------
Private Sub Command12_Click()
    On Error GoTo Command12_Click_Err

    Dim strUserFName As String
    Dim strUserSName As String

    ' if [BarcodeCI] is numeric
    strUserFName = DLookup("Forename", "tblLoan", "[Barcode Number] = " & [Forms]![frmCheckIn]![BarcodeCI])
    strUserSName = DLookup("Surname", "tblLoan", "[Barcode Number] = " & [Forms]![frmCheckIn]![BarcodeCI])

    'if [BarcodeCI] is Text, uncomment the following two lines and comment out the two DLookups above
  '   strUserFName = DLookup("Forename", "tblLoan", "[Barcode Number] = '" & [Forms]![frmCheckIn]![BarcodeCI] & "'")
  '   strUserSName = DLookup("Surname", "tblLoan", "[Barcode Number] = '" & [Forms]![frmCheckIn]![BarcodeCI] & "'")


    DoCmd.OpenQuery "qryCheckInAppend", acViewNormal, acEdit
    DoCmd.OpenQuery "AvailableYesUpdate", acViewNormal, acEdit
    DoCmd.OpenQuery "qryCheckInDelete", acViewNormal, acEdit
    Beep
    
    MsgBox "Thank you " & strUserFName & " " & strUserSName & vbCrLf & "Book successfully returned."
    DoCmd.Close acForm, "frmCheckIn"
    DoCmd.OpenForm "frmCheckIn", acNormal, "", "", , acNormal



Command12_Click_Exit:
    Exit Sub


Command12_Click_Err:
    MsgBox Error$
    Resume Command12_Click_Exit


End Sub

[Emma G]

Thank you all for your comments. Ssanfu that coding worked like a charm! Thank you!

Now that I have solved that problem. I will move onto addressing the design of my database with your comments and suggestions in mind.

Thanks again.

Emma