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?
 
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
 
Paul Cook-GilesAccess/VBA/SQL Database 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.