Pass query result to a msgbox

[hardy1976]

I have a query that returns a list (between 2-5 rows) of numbers. I want those numbers to appear in a message box is this possible? if so how?

I know I could create a form to do it however was wondering if there is a simpler way.

Thanks
H.

 

[pr2-eugin]

Hello hardy1976, you might have to use some VBA here.. Open the Query and assign it to a Recordset object, loop through it and then add the data into a string and finally append the string to the msgbox..

Something along the lines of..

Private Sub showInMsgBox()
    Dim rstObj As DAO.Recordset, msgStr As String
    Set rstObj = CurrentDB.OpenRecordset("yourQueryName")
    Do While Not rstObj.EOF 
        msgStr = msgStr & rstObj.Fields("thFieldName") & vbCrLf
        rstObj.MoveNext
    Loop
    Call MsgBox(msgStr)
    Set rstObj = Nothing
End Sub

 

[hardy1976]

Thankyou thats exactly the sort of thing I was looking for.

I get an error... Runtime error 3061, too few parameters. Expected 4? on this line...Set rstObj = CurrentDb.OpenRecordset("query2")

 

[pr2-eugin]

Does your Query need any input parameters to run the Query? If so you should pass the parameters to the Query..

Private Sub showInMsgBox()
    Dim qdf As DAO.QueryDef
    Dim rstObj As DAO.Recordset, msgStr As String
    
    Set qdf = CurrentDB.QueryDefs("[COLOR=Blue]yourQueryName[/COLOR]")

    'Supply the parameter value
    qdf.Parameters("[COLOR=Blue]theParameter1[/COLOR]") = passValue1
    qdf.Parameters("[COLOR=Blue]theParameter2[/COLOR]") = passValue2
    qdf.Parameters("[COLOR=Blue]theParameter3[/COLOR]") = passValue3
    qdf.Parameters("[COLOR=Blue]theParameter4[/COLOR]") = passValue4

    Set rstObj = qdf.OpenRecordset()
    Do While Not rstObj.EOF 
        msgStr = msgStr & rstObj.Fields("[COLOR=Blue]thFieldName[/COLOR]") & vbCrLf
        rstObj.MoveNext
    Loop
    Call MsgBox(msgStr)
    Set rstObj = Nothing
    Set qdf = Nothing
End Sub

All Blue Bits should match how they have been defined..

 

[hardy1976]

Thanks - I had to go learn about DAO.QueryDefs but thats solved my issue. Many thanks.

PS I went to give you thanks and I cant see the button!

 

[pr2-eugin]

Thanks - I had to go learn about DAO.QueryDefs but thats solved my issue. Many thanks.

Not to worry, glad to have helped (??!!)

PS I went to give you thanks and I cant see the button!

There was a problem earlier with the site, the button is back on, i.e. if you still think my post was helpful..

 

[leonf]

Hello hardy1976, you might have to use some VBA here.. Open the Query and assign it to a Recordset object, loop through it and then add the data into a string and finally append the string to the msgbox..

Something along the lines of..

Private Sub showInMsgBox()
    Dim rstObj As DAO.Recordset, msgStr As String
    Set rstObj = CurrentDB.OpenRecordset("yourQueryName")
    Do While Not rstObj.EOF 
        msgStr = msgStr & rstObj.Fields("thFieldName") & vbCrLf
        rstObj.MoveNext
    Loop
    Call MsgBox(msgStr)
    Set rstObj = Nothing
End Sub

Is there a way to set properties to the above, similar to the MsgBox properties (i.e. Title, Message Type (Information, Critical) etc.)? I tried doing the same using the MsgBox, however it just looped. Here is the code that I would like to enhance and attached is the image of the working function. Thank you in advance for your assistance.

Public Function showInMsgBox()
Dim rstObj As DAO.Recordset, msgStr As String
Set rstObj = CurrentDb.OpenRecordset("qryNo_Match")
Do While Not rstObj.EOF
msgStr = msgStr & rstObj.Fields("[Anvil Code Description]") & " - " & _
rstObj.Fields("[Arts ID]") & vbCrLf
rstObj.MoveNext
Loop
Call MsgBox(msgStr)
Set rstObj = Nothing
End Function

 

[leonf]

Is there a way to set properties to the above, similar to the MsgBox properties (i.e. Title, Message Type (Information, Critical) etc.)? I tried doing the same using the MsgBox, however it just looped. Here is the code that I would like to enhance and attached is the image of the working function. Thank you in advance for your assistance.

Public Function showInMsgBox()
Dim rstObj As DAO.Recordset, msgStr As String
Set rstObj = CurrentDb.OpenRecordset("qryNo_Match")
Do While Not rstObj.EOF
msgStr = msgStr & rstObj.Fields("[Anvil Code Description]") & " - " & _
rstObj.Fields("[Arts ID]") & vbCrLf
rstObj.MoveNext
Loop
Call MsgBox(msgStr)
Set rstObj = Nothing
End Function

Can anyone please help. I have no responses on my inquiry, so I am reposting.

 

[Grumm]

I'm not 100% sure of what you actually wants, but you can give some options to the MsgBox function : https://msdn.microsoft.com/en-us/library/139z2azd(v=vs.90).aspx

So if you want a title, just change the line :

Call MsgBox(msgStr)

into

Call MsgBox(msgStr, ,"This is your title")

The middle option is for the style. Look in the link i gave you. There are a ton of different styles and you can combine different styles. (see example in link)

 

[leonf]

Thank you Grumm, I think that will do it....I will try it then report back...