search button

[kardiffBlue]

i'm new to VB and am hoping for some help.
i want to create a form that has a text box and command button at the top, so that when i enter a number in the textbox it searches a table in the database and then brings up some info from the table.
so for instance: textbox is textbox1, button is cmdFind, table is tbltest

in table test is a list of companies and their details. so when i type 100 in the textbox and hit find, it will bring up the company name, address etc.

i'm guessing this is fairly easy but i cant really find it in a book and so would really like a bit of code that i can copy and then alter to my needs. i think it will be recordset, select, like - etc.
can anyone help please?

[lotus18]

You can do that by using Where clause in your sql statement.

E.g [CODE=vb]"Select * From <TableName> Where <FieldName>=' " & Text1.Text & "'"[/CODE]

It is much better if your field name here is the PK.

Rey Sean

[tomarvijay80]

Hi kardiffBlue,

Hope this code will help u. In this I am using access database. u can change database and table as per ur requirement.


Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim sql As String

Private Sub cmdFind_Click()
cn.ConnectionSt ring = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=D:\Visua l Basic\text\comp any.mdb;Persist Security Info=False"
cn.Open
rs.CursorLocati on = adUseClient
sql = "SELECT * FROM company WHERE ID=" & textbox1.Text & ""
rs.Open sql, cn, adOpenForwardOn ly, adLockReadOnly
MsgBox "id: " & rs("id")
MsgBox "city: " & rs("city")
MsgBox "state: " & rs("state")
rs.Close
cn.Close
End Sub