Search a Record from access database using VB 6

In this article I am going to show you how to search a record from an Access database using Microsoft Visual Basic 6.0.

First of all design an interface as shown in the image. Here I have a search box where I am going to enter the value based on which I am going to search a record from an access table. After clicking the search button the result will be displayed in the corresponding text boxes.

Now, create a connection to the database. After you successfully created a connection to the database double click the search button and write the following code in the click event of the search button:

 Private Sub Search_Click()  
 On Error GoTo z  
 If Trim(txtsearch.Text) = Trim("") Then  
 MsgBox "Please enter receipt_no", vbInformation, "Search receipt_no"  
 text1.SetFocus  
 Exit Sub  
 End If  
 On Error Resume Next  
 Call Connection ' connection to the database  
 With rs  
 If .State = adStateOpen Then .Close  
 .Open " select * from patients where Receipt_No = " & Trim(Val(txtsearch.Text)) & "", Con, adOpenDynamic, adLockPessimistic  
 If .RecordCount <= 0 Then  
 Dim msg  
 msg = MsgBox("No Record Found", vbInformation + vbOKOnly, "Record")  
 text1.SetFocus  
 .Close  
 text1.Text = ""  
 Exit Sub  
 End If  
 End With  
 With rs  
 Dim c As Double  
 c = Val(txtsearch.Text)  
 If .State = adStateOpen Then .Close  
 .Open "select * from patients where Receipt_No = " & c & "", Con, adOpenDynamic, adLockPessimistic  
 Do Until .EOF  
 txtreceipt.Text = .Fields("Receipt_No").Value  
 txtopd.Text = .Fields("OPD_MRD").Value  
 txtname.Text = .Fields("Patientname").Value  
 txtrecdate.Text = .Fields("Receipt_date").Value  
 txtrectime.Text = .Fields("Receipt_time").Value  
 .MoveNext  
 Loop  
 .Close  
 End With  
 Exit Sub  
 z:  
 MsgBox "Please enter valid receipt_no", vbInformation, "Enter receipt_no"  
 Exit Sub  
 End Sub  

VB6