Save query results to an array

Question

good afternoon!

I need to get the results of this query:

sql = "SELECT controle.ID FROM controle WHERE BP = '" & controlectform.nmbpbox.Value & "';"

And save to an array. Later I’ll do a for each on that array and use the results stored in a new query.

For Each I have done but the data is not entered in my array, so the query only runs the first time.

Below is the complete code.

Function InsereDados()

    Dim vArray As Variant
    Dim vContador As Integer
    Dim cn  As ADODB.Connection
    Dim rs  As ADODB.Recordset
    Dim arrid() As String
    Dim toid As Variant


    Set cn = New ADODB.Connection

    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & enderecoDB & ";Jet OLEDB:Database"

    cn.Open

    Set rs = New ADODB.Recordset

    sql = "SELECT controle.ID FROM controle WHERE BP = '" & controlectform.nmbpbox.Value & "';"

    rs.Open sql, cn

    For i = 1 To rs.RecordCount

    arrid(0) = rs(0)

    Next

    For Each ID In arrid

    Call SelectIDENT
    Call SelectBENEFIT
    Call SelectBP
    Call SelectCPF
    Call SelectNome
    Call SelectADM
    Call SelectFilial
    Call SelectSOLICITANTE
    Call SelectDTSOLIC
    Call SelectRECEBIMENTO
    Call SelectENVIO
    Call SelectRETIROU
    Call SelectMINUTA
    Call SelectCARTAO

    vArray = Array("", SelectIDENT, SelectBENEFIT, SelectBP, SelectCPF, SelectNome, SelectADM, SelectFilial, SelectSOLICITANTE, _
        SelectDTSOLIC, SelectRECEBIMENTO, SelectENVIO, SelectRETIROU, SelectMINUTA, SelectCARTAO)

    count = 2

        With Worksheets("Planilha1")
           For vContador = 1 To UBound(vArray)
           .Cells(count, vContador).Value = vArray(vContador)
            Next vContador
        End With
    count = count + 1

   Next

End Function

Answer 1

Your problem is using the recordcount property as a check for your [FOR]. recordcount will only have information after you have gone through recordset. As a solution I suggest:

option 1: Before doing the [FOR] . . . rs.movelast rs.movefirst . . .

Option 2: Use the EOF property (End of file). This property has false value until it reaches the end of the recordset, ie the last line.

Another point is that as you will fill your array and you do not know the amount of records returned, you should resize the size of your array, in this case you should use the RESIZE command.