How to search from a database, selecting from a combo or list box in vb?

[shahriar2403]

Let, i have a database in ms access with two field named-"name" & "roll". I also have a combo box or list box in vb containing those names. I have to select a name from that list and appear the corresponding "roll" from that database in a text box in vb form. How can i do it?

[choudhuryshouvi]

u need to first open the database and assign its table's contents to a recordset object. all these u have to do in form_load event. try this code snippet :-

'in general section
dim db as database,rs as recordset

'in form_load event
set db=opendatabase(app.path & "\yourdbname.mdb")
set rs=db.openrecordset("yourtablename",dbopentable)
if rs.recordcount>0 then rs.movefirst

'in listbox click event
set rs=db.openrecordset("select roll from yourtablename where name='" & list1.text & "'",dbopendybaset)
if rs.recordcount>0 then
text1.text=rs("roll")
endif
set rs=nothing

[pbrookee]

In vb form_load() put in code to which will fill your listbox with the database info :
'your code here to connect to db
'sql line to select from db
' sql = ("Select * from db_name")
rec.Open sql, cn, adOpenKeyset, adLockPessimistic
While Not rec.EOF
Me.listbox.AddItem rec(1).Value
rec.MoveNext

'click and data shows up in text box
Private Sub listbox_Click()
Dim sql As String
'connect to db

For intXfor = 0 To Me.listbox.ListCount - 1
'when user is selected populate data from table into txtboxes
If Me.listbox.Selected(intXfor) = True Then

sql = ( _
"Select * from db_name where row = '" _
& Me.listbox.List(intXfor) & "'")

rec.Open sql, cn, adOpenKeyset, adLockPessimistic
'display data in textbox
Me.textbox_name.Text = rec(0).Value

rec.Close
Set rec = Nothing
cn.Close
End If
Next
End Sub