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.