Array from ADO recordset, then display in a mesaage box

Exactly what the title says....
This is the code I have, but I don't know ow to display the array in a mesage box....

   Set adoMRS2 = New ADODB.Recordset
   With adoMRS2
                  strSql2 = "Select Product_ID from tracking_file Where Product_ID <> '" & strDTID & "' AND field_1 ='" & strSS & "'"

             .Open strSql2, adoMCN2, , , 1
            End With
If Not adoMRS2.EOF Then
arr1 = adoMRS2.GetRows

MSGBOX "These are the results: " & arr1

I would prefer to have each record on a anew line, so I suppose I need to insert &Chr(13) betwen each.
lexoAsked:
Who is Participating?
 
CetusMODConnect With a Mentor Commented:
PAQed, with points refunded (500)

CetusMOD
Community Support Moderator
0
 
bkthompson2112Commented:
Hi lexo,

You'll need to loop through arr1 and add each field of each record to a string.
after each record add a vbCrLf.

then use that string in the msgbox.

Be careful, according to msdn: "The maximum length of prompt is approximately 1024 characters, depending on the width of the characters used."
see here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctMsgBox.asp

maybe a better way to do this would be to loop throught arr1 and use debug.print to send the results to the immediate window.

bkt
0
 
bramsquadCommented:
'do everything to get your array here

        Dim arr1() As String

        Dim i As Integer, total As String
        For i = 0 To arr1.Length - 1
            total += arr1(i) + vbCrLf
        Next

msgbox( "These results are:" + vbcrlf + vbcrlf + total )

~b

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
bramsquadCommented:
just to note, vbcrlf is the visual basic constant for a carriage return, i know you mentioned Chr(13)
0
 
lexoAuthor Commented:
it errors at .Length.  I am using a very toned down version of Vb in a macro. I don't think it sees that as a function.
0
 
bramsquadCommented:
im using .net, prolly why.....try this

For i = 0 To UBound(arr1) - 1

~b
0
 
StimphyCommented:
Try this...

Join(Arr1,vbCrLf)


Regaurds,
Dave
0
 
JR2003Commented:
The GetRows method is used to copy records from a Recordset object into a variant that is a two-dimensional array. The variant array is automatically dimensioned (sized) to fit the requested number of columns and rows. To allow backwards compatibility with earlier versions of ADO, the columns are placed in the first dimension of the array and the rows are placed in the second dimension.

Dim arr1 as Variant
Dim i as Long
Dim j As Long

 Set adoMRS2 = New ADODB.Recordset
   With adoMRS2
               strSql2 = "Select Product_ID from tracking_file Where Product_ID <> '" & strDTID & "' AND field_1 ='" & strSS & "'"

             .Open strSql2, adoMCN2, , , 1
            End With
If Not adoMRS2.EOF Then
arr1 = adoMRS2.GetRows

For i = LBound(arr1, 1) to UBound(arr1, 1)
    sLine = ""
    For j LBound(arr1, 2) to UBound(arr1, 2)
        sLine = sLine & arr1(j, i) & ", "
    Next j
    sLine = Left(sLine, Len(sLine) -2)
    Debug.Print sLine
    MsgBox "Line " & i & " " & sLine
Next i

0
 
StimphyCommented:
I think what you are doing now will probably be your best alternative.

Regaurds,
Dave
0
 
lexoAuthor Commented:
Ok I got it to work, but didn't use the code provided to me here.  Thank you for your time!!

arr1 is a string.

If Not adoMRS2.EOF Then
Do Until adoMRS2.EOF = True
   arr1=  arr1 & adoMRS2("Loan_ID") & vbCrLf
   adoMRS2.MoveNext
Loop
MsgBox "Results:  " & vbCrLf & arr1
End If
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.