VBA Access & Excel: Carregando dados em um FORM no Excel, a partir de dados disponíveis no Access.

Function ReadcbDisciplina()
    ' Author:                           Date:                     Contact:
    ' André Bernardes             22/07/2008 11:35    bernardess@gmail.com
    ' Carrega dados no Combo "cbDisciplina".

    On Error GoTo ErrorHandler              ' Ative a rotina de tratamento de erro.

    ' Define Vars.
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset

    Dim k As Long
    Dim stDB As String
    Dim stSQL As String
    Dim vaData As Variant

    ' Efetua a conexão.
    Set cnt = New ADODB.Connection                                                  ' Inicia a conexão do objeto (Instantiate the Connection object).
    Let stDB = dbTarget                                                             ' Path to and the name of the database. Assumes the DB and worksheet are in same directory. If not, you need to specify DB path

    ' Limpa o Combo.

    ' Constrói a consulta SQL.
    Let stSQL = stSQL & "SELECT DISTINCT tbl_data_Disciplinass.Sigla, tbl_data_Disciplinass.Disciplina" & Space(1)
    Let stSQL = stSQL & "FROM tbl_data_Disciplinass" & Space(1)
    Let stSQL = stSQL & "WHERE (((tbl_data_Disciplinass.Active) = -1))" & Space(1)
    Let stSQL = stSQL & "ORDER BY tbl_data_Disciplinass.Disciplina"

    With cnt
        .CursorLocation = adUseClient       ' Necesary for creating disconnected recordset.
        .Open stConn                        ' Open connection.

        Set rst = .Execute(stSQL)           ' Instantiate the Recordsetobject and execute the SQL-statement.
    End With

    With rst
        Set .ActiveConnection = Nothing     ' Disconnect the recordset.
        Let k = .Fields.Count

        vaData = .GetRows                   ' Populate the array with the whole recordset.
    End With

    cnt.Close                               ' Close the connection.

    'Really manipulate the Combobox's properties and show the data.
    With Me
        With .cbDisciplina
            .BoundColumn = k
            .Column = vaData                ' ? vaData(0,0),vaData(1,0),vaData(2,0) - (? vaData(coluna,linha),vaData(coluna,linha),vaData(coluna,linha))
            .ListIndex = -1
        End With
    End With

    Let FirstLoad = True
    Set rst = Nothing                       ' Tira os objetos da memória (Release objects from memory).
    Set cnt = Nothing

ErrorHandler:                           ' Rotina de tratamento de erro.
    Call ErrorShow(Err.Number, Err.Description, Err.Source)
End Function

André Luiz Bernardes
Related Posts Plugin for WordPress, Blogger...


diHITT - Notícias