SQL inquiry

[junyor]

People,

I have a table called "User" and I need to get the "Perfil" field when customer informs the number "numero" field

Table - user:

numero - perfil
1 junior
2 master
3 pleno

something like that:
There is a text box (txtnumero) and a button (cmdbusca) on the form,
Once the number is entered and the button cmdbusca is clicked, I need to get the perfil field accordingly.

Does anyone know how to do this?

 

[pbaldy]

Welcome to the site. The simplest way would be to use a combo box instead of the textbox. The row source of the combo would be a query that returned both fields from that table. Then to get the second field you'd refer to the second column:

Forms!FormName.ComboName.Column(1)

 

[junyor]

Hi pbaldy!!!

But the problem is that the table has about 60000 records and it might be increased.

Is there any way to use a sql code to search the data from a table?

like:

"Select perfil from user where numero=txtnumero"

Tks.

 

[pbaldy]

Sure; you can use a DLookup() function or open a recordset with that SQL. The simplest for some people is the DLookup:

DLookup("perfil", "user", "numero = " & Me.txtnumero)

More info on them here:

http://www.mvps.org/access/general/gen0018.htm

 

[junyor]

Sorry for my ignorance but it didn't work... a message appeared:
Complile error: Expected: =

The code is as follows:

Private Sub cmd_Click()
DLookup("perfil", "user", "numero = " & Me.txtnumero)
End Sub

I was trying to do something like below but it didn't work too...

Private Sub cmd_Click()
Dim varPesquisarEst As Recordset
Set varPesquisarEst = DBEngine(0)(0).OpenRecordset("Select * FROM user WHERE numero =" & Me.txtnumero)
If varPesquisarEst.RecordCount > 0 Then
.....
Else
MsgBox "Estabelecimento não encontrado", _
vbInformation, "Atenção!"
End If
End Sub

Do you have a suggestion?

Tks.

 

[pbaldy]

Well, you can't just have the DLookup by itself like that. What are you trying to accomplish? Following your second logic:

If Not IsNull(DLookup("perfil", "user", "numero = " & Me.txtnumero)) Then
  ...
Else
  MsgBox "Estabelecimento não encontrado", _
    vbInformation, "Atenção!"
End If

 

[junyor]

Hi Pbaldy!

I'd like to thank you, it's working now!

Tks a lot

 

[pbaldy]

No problem;glad to hear it's working for you.