Still working on this problem. I now have a search string but even if I don't use the concatenated strIN as soon as it starts it EOF is TRUE. so I never see the rest of the code, not sure if the filling of the Test2 and Test2 Arrays will work.
Code:
 Dim rs2 As New ADODB.Recordset
        'A String variable into which you will build part of your
        'WHERE clause using the SQL In Operator:
        Dim strIN As String
        Dim Test1() As String
        Dim Test2() As String
        Dim X As Integer

        strIN = ""


        'Use those two to concatenate your parameters from your array:
        For X = 1 To CBDrHDPLTCount
            strIN = strIN & "," & CBDrHDPLTPn(X)
        Next

        'Nip the first commo from the front of the concatenated string
        'strIN = Replace(strIN, ",", "", 1, 1)
        strIN = "600035-1125"

        X = 1

        rs2.Open("SELECT Part_Number, Part_Desc " & _
           "FROM tblMain " & _
           "WHERE Part_Number In(" & strIN & ")", strConn2)

        While Not rs2.EOF

            If CBDrHDPLTPn(X) > "" And rs2.Fields("Part_Number").Value = CBDrHDPLTPn(X) Then

                Test1(X) = rs2.Fields("Part_Number").Value  'Part number from Database
                Test2(X) = rs2.Fields("Part_Desc").Value    'First of the items to pull from the data base

                X = X + 1
            End If
        End While

        rs2.Close(