SQL Query VBA Excel

Asked

Viewed 2,427 times

0

Good afternoon!

I have a problem with a query in Access via form.

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

Below is the complete code.

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

If BP option is checked, it calls the function and passes to it the BP number typed 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 takes the parameters and assembles the query query in the database through the sql variable. This is where 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

I mean, I call Function:

Call Selectnome()

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

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

My criteria is Where BP.

sql query excel vba ms-access

  • The way it’s written, man While will go through all the records of your query and only when you reach the end of the table (EOF) the loop will be terminated and then the value of NOMEDB copied to the control. The problem is in the where that’s not working? That’s it?

  • Exactly. The data is all formatted as text. But Where does not work at all.

  • Then post your where to understand it.

2 answers

0

Ishmael,

Below is the complete code.

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

If the BP button is marked, it calls the function and passes to it the BP number typed 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 takes the parameters and assembles the query query in the database through the sql variable. This is where 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

I mean, I call Function:

Call Selectnome()

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

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

My criteria is Where BP.

  • Hello, I suggest you edit your question by placing all the codes and contents within the question for registration purposes.

  • That’s right Humberto, edit your question with the data you put here in this part, since it is intended only for the answer. When your question, you passed the value "BP" (in quotes) to the parameter Criterion, but, in this case, the value of the variable is not being passed, and yes, the text "BP".

  • I put the information in the question field as directed. And I made the following change to the code: I put "Byval" in the criterion, and that’s: Function Selectname(Table As String, Field As String, Byval Criteria As String) But now the code gives error. An error message appears at runtime: No value Given for one or more required Parameters. I know it’s simple to solve, but I have a very basic knowledge of vba and I’m still crawling in the correct syntax.

0


After much breaking my head, I was able to make the consultation.

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 calling the function.

Now the consultation works right.

Browser other questions tagged sql query excel vba ms-access

You are not signed in. Login or sign up in order to post.