Searching access using VB

[Murali1311]

Hi, i am doing a payroll calculation using vb and access. In this, if a user clicks the search button, it should ask the emp_no and if it is present, it should display it. for this i need the codings. I tried some codings, but it doesn't works properly

Additional Information
I connected ms access with vb using addins manager.So give the code related to this. thanks in advance

[debasisdas]

Try to implement the search logic using SQL .

[dandonia]

hi can i ask why you are add ins manager to connect the database?

its simple stuff using other methods so is there a specific reason you are using yours.

[choudhuryshouvi]

Hope this code can cover up your requirement.

Before try to run this code create a database "employee.mdb" and a table "info" with four fields "emp_no","name","basic","tax". Add some records to test the code. Take a button(cmdsearch) and five textboxes(text1 to text5) and associated five labels. Below is a sample interface that i've used. See it for your consideration.

Regards,
Shouvik

Option Explicit

Private Sub cmdsearch_Click()
Dim db As Database
Dim rs As Recordset
Dim str As String
Dim confirm As Integer

begin:
str = InputBox("Enter Employee No. :", "Search")
If str <> "" Then
Set db = OpenDatabase(App.Path & "\employee.mdb")
Set rs = db.OpenRecordset("select * from info where emp_no='" & str & "'")
If rs.RecordCount > 0 Then
Text1.Text = rs!emp_no
Text2.Text = rs!Name
Text3.Text = rs!basic
Text4.Text = rs!tax
Text5.Text = rs!basic - rs!tax
Else
confirm = MsgBox("Sorry, Employee No. " & Chr(34) & str & Chr(34) & " was not found." & vbCrLf & "Try again?", vbYesNo + vbQuestion, "Search")
If confirm = vbNo Then
Exit Sub
Else
GoTo begin
End If
End If
Set rs = Nothing
Else
confirm = MsgBox("Invalid employee no." & vbCrLf & "Try once again?", vbYesNo + vbQuestion, "Search")
If confirm = vbNo Then
Exit Sub
Else
GoTo begin
End If
End If
End Sub

[jireh]

Hi dandonia!
You can use Addins if you dont have MS Access installed

[jireh]

Hi Murali1311!
try this one too.

Dim dbase As Database
Dim tmpRs as Recordset

Set dbase=OpenDatabase(App.Path & "\Sample.mdb")
Set tmpRs=dbase.OpenRecordset("SELECT * from [table info] where EmployeeNumber='" & Searchtxt.Text & "'")
  If Not tmpRs.BOF Then
       With tmpRs
           text1.Text = !employeename
           text2.text = !Address
           ...and so on
       End With
   Else
      MsgBox "Employee Number not found.", VBInformation
   End If

[dandonia]

Hi dandonia!
You can use Addins if you dont have MS Access installed

K, thanks. I didn't know he didnt have Access installed.

[Murali1311]

hi can i ask why you are add ins manager to connect the database?
Hi, Thanks for your reply

its simple stuff using other methods so is there a specific reason you are using yours.

Hi, Thanks for your reply

Actually, I feel that connecting Access with VB using a Visual manager will be simple and easy. So that no extra coding will be needed. Extra coding in the sense
dim rs as recordset e.t.c

So, i don't want to connect the database(MS Access) using the codings. I connected the database already. My problem is the search option is not working properly. Now i cured the problem. BUt still i can't search a item, If it contains alphanumeric characters. Say, asp345.

Give a coding for this. Thanks in advance

[choudhuryshouvi]

use the "Like" operator

Sample sql :-

"select * from employee where emp_name like '*S*'"

[debasisdas]

As already suggested ,try to implement the search logic using SQL . Try to use Pattern matching using LIKE search.

[jireh]

Hi, Thanks for your reply

Actually, I feel that connecting Access with VB using a Visual manager will be simple and easy. So that no extra coding will be needed. Extra coding in the sense
dim rs as recordset e.t.c

So, i don't want to connect the database(MS Access) using the codings. I connected the database already. My problem is the search option is not working properly. Now i cured the problem. BUt still i can't search a item, If it contains alphanumeric characters. Say, asp345.

Give a coding for this. Thanks in advance

you mean you use manual connection? what if the path will be changed like for example the folder where you have saved your project will moved to another directory. Does it give no error?