SQL Query VBA Excel

0

Good afternoon!

I have a problem with an Access query via form.

The form is from the Excel vba, which connects to the database.

The full code is below.

This is the button code. Clicking it validates which of the two buttons is marked (true). If neither is selected it shows an error message.

If BP option is checked, it calls the function and passes it the BP number entered by the user in the input box. The same happens if the CPF is selected.

Private Sub btn_consulta_Click()

    Dim BP      As String
    Dim cpf     As String

    BP = controlectform.nmbpbox.Value
    cpf = controlectform.nmcpfbox.Value

    If controlectform.optbp.Value = True Then

        Call SelectNome("controle", "NOME", "BP")

        Exit Sub

    ElseIf controlectform.optcpf.Value = True Then

        Call SelectNome("controle", "NOME", "cpf")

        Exit Sub

    Else

        MsgBox "Selecione a opção de consulta!", vbCritical

    End If

End Sub

The function receives the parameters and mounts the search query in the database through the sql variable. It is in it that the where is. But it does not work.

    Function SelectNome(Tabela As String, Campo As String, Criterios As String) As Variant
    Dim NOMEDB As Variant

    Dim sql As String
    Dim cn  As ADODB.Connection
    Dim rs  As ADODB.Recordset

    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" & Table & "." & Field & "From" & Table & "where" & Criteria & ";"

rs.Open sql, cn

If Not rs.EOF Then
Do While Not rs.EOF
NOMEDB = rs(0)
rs.MoveNext
Loop
End If

cn.Close

controlectform.nomecolaboradorbox.Value = NOMEDB

End Function

That is, I call function:

Call SelectNome ()

And I pass the arguments following the order, Table, Field, Criteria. In this case:

Call SelectNome ("control", "NAME", "BP")

My criterion is Where BP.

    
asked by anonymous 28.03.2017 / 16:58

2 answers

0

After much breaking, I was able to query.

Below is the code developed

Function SelectNome()

    Dim NOMEDB As Variant

    Dim sql As String
    Dim cn  As ADODB.Connection
    Dim rs  As ADODB.Recordset

    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.NOME FROM controle WHERE BP = '"_ 
& controlectform.nmbpbox.Value & "';"

    rs.Open sql, cn

    If Not rs.EOF Then
        Do While Not rs.EOF
            NOMEDB = rs(0)
            rs.MoveNext
        Loop
    End If

    cn.Close

    controlectform.nomecolaboradorbox.Value = NOMEDB

End Function

As you can see the only change I made was to mount the sql query myself, instead of passing the parameters by the function call.

Now the query works fine.

    
29.03.2017 / 16:05
0

Ismael,

The full code is below.

This is the button code. Clicking it validates which of the two buttons is marked (true). If neither is selected it shows an error message.

If the BP button is checked, it calls the function and passes it the BP number entered by the user in the input box. The same happens if the CPF is selected.

Private Sub btn_consulta_Click()

    Dim BP      As String
    Dim cpf     As String

    BP = controlectform.nmbpbox.Value
    cpf = controlectform.nmcpfbox.Value

    If controlectform.optbp.Value = True Then

        Call SelectNome("controle", "NOME", "BP")

        Exit Sub

    ElseIf controlectform.optcpf.Value = True Then

        Call SelectNome("controle", "NOME", "cpf")

        Exit Sub

    Else

        MsgBox "Selecione a opção de consulta!", vbCritical

    End If

End Sub

The function receives the parameters and mounts the search query in the database through the sql variable. It is in it that the where is. But it does not work.

    Function SelectNome(Tabela As String, Campo As String, Criterios As String) As Variant
    Dim NOMEDB As Variant

    Dim sql As String
    Dim cn  As ADODB.Connection
    Dim rs  As ADODB.Recordset

    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" & Table & "." & Field & "From" & Table & "where" & Criteria & ";"

rs.Open sql, cn

If Not rs.EOF Then
Do While Not rs.EOF
NOMEDB = rs(0)
rs.MoveNext
Loop
End If

cn.Close

controlectform.nomecolaboradorbox.Value = NOMEDB

End Function

That is, I call function:

Call SelectNome ()

And I pass the arguments following the order, Table, Field, Criteria. In this case:

Call SelectNome ("control", "NAME", "BP")

My criterion is Where BP.

    
28.03.2017 / 17:52