Combine query with message box to display results of query

I have the following query:
SELECT tMain.TrayNo, tMain.LensSupplierNo
FROM tMain
WHERE (((tMain.LensSupplierNo)="Tx123"));

I would like to displays this result in a message box... and I would like to have use VBA instead of a saved query.  Is this possible in some way?

EX: MSGBOX "Stuff found: " & SELECT tMain.TrayNo, tMain.LensSupplierNo FROM tMain WHERE (((tMain.LensSupplierNo)="Tx123"));

Or maybe put the results from the query into a variable somehow to use in with the message box?
thandelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
dim strLensSupplierNo as String, strFound as String
strLensSupplierNo = "Tx123"

strFound = DLookup("TrayNo", "tMain", "LensSupplierNo = '" & strLensSupplierNo  & "'") & " " & DLookup("LensSupplierNo ", "tMain", "LensSupplierNo = '" & strLensSupplierNo  & "'")

MsgBox "Stuff found: " & strFound
0
thandelAuthor Commented:
Thank you let me try... will this return more  than one result (TrayNo)?
0
thandelAuthor Commented:
This works well but when there is more than one order that meets the query, the string is only capturing a single TrayNo.  There can be more than one TrayNo that meets this criteria and I need to display all TrayNos that meet it.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Gustav BrockCIOCommented:
Then a messagebox is badly suited.

Create a very basic popup and modal form with a single ListBox control.
Use your query to fill the listbox when the form is opened.
Much cleaner and neater.
0
thandelAuthor Commented:
OK but how do I fill the listbox on the form using a query?
0
Gustav BrockCIOCommented:
The simplest method is to use the wizard. It opens when you insert the listbox control on the form (in design view).

But use the query as the RecordSource of the listbox.
0
thandelAuthor Commented:
OK thanks I'll give it a try... but isn't there just a way to run a SQL query and place the result into a string... then I can just pop that into a message box without the need for a form / table.
0
thandelAuthor Commented:
I had this in my notes... but its not finding anything

  
    sSQL = "SELECT * FROM TMain WHERE LensSupplierNo = 'TxNova'"

    Set rs = CurrentDb.OpenRecordset(sSQL)
    
    With rs
        Do Until .EOF
             test = test & " " & !LensSupplierNo
             .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    
    MsgBox test

Open in new window

0
Gustav BrockCIOCommented:
Set its property RowSource: SELECT * FROM TMain WHERE LensSupplierNo = 'TxNova'
and property RowSourceType: Table or query

That should do it.
0
thandelAuthor Commented:
I'm sorry set the property of ...?
0
thandelAuthor Commented:
I have it now working in the message box... I just has the wrong variable being pulled

sSQL = "SELECT * FROM TMain WHERE LensSupplierNo = 'TxNova'"

    Set rs = CurrentDb.OpenRecordset(sSQL)
    
    With rs
        Do Until .EOF
             Test = Test & " " & !TrayNo
             .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    
    MsgBox Test

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
The listbox - we discussed a simple form with a listbox and the wizard for this.

Your solution will work, but it is not needed at all - the listbox handles this automatically when setting the RowSource from a query.
0
thandelAuthor Commented:
Agreed but it my initial question was looking for a message box solution.  If this was not possible then I would have used your solution.
0
thandelAuthor Commented:
Other solutions were accurate but not what I was looking for.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.