Option Explicit

Dim db As Database
Dim rs As Recordset

''performing the addition operation with the validation
Private Sub Command1_Click()
If Trim(txtempname.Text) <> "" Then
    If IsEmpExists(Trim(txtempname.Text)) Then
        MsgBox "The employee " & Chr(34) & Trim(txtempname.Text) & Chr(34) & " already exists in the database." & vbCrLf & _
            "Please use a different name rather than this one.", vbInformation, "Duplicate Employee"
        txtempname.SelStart = 0
        txtempname.SelLength = Len(Trim(txtempname.Text))
        txtempname.SetFocus
    Else
        rs.AddNew
        If rs.EditMode = dbEditAdd Then
            rs!empname = Trim(txtempname.Text)
            rs.Update
            Call LoadEmployees
            MsgBox "New employee created.", vbInformation, "Done"
            txtempname.Text = ""
            txtempname.SetFocus
        End If
    End If
Else
    MsgBox "The employee name should not be null.", vbCritical
    txtempname.SetFocus
End If
End Sub

Private Sub Form_Load()
''making connection to the database and opening the employee table to retrieve existing records
Set db = OpenDatabase(App.Path & "\employee.mdb")
Set rs = db.OpenRecordset("details", dbOpenTable)

If rs.RecordCount > 0 Then
    rs.MoveFirst
Else
    MsgBox "No employee details found." & vbCrLf & _
        "Please add some records.", vbInformation
End If
''creating the list of existing employees
Call LoadEmployees
End Sub

''this is the core function which is responsible for the entire validation process to be executed. this function takes
''the employee name as an arguement that you wish to check whether already exists in the database or not. the function
''checks for its first occurance. if found then it returns true otherwise returns false. "true" means the employee
''does already exist and "false" means not.
Public Function IsEmpExists(ByVal e_name As String) As Boolean
Dim rsd As Recordset    ''declarigng a recordset object specially for this operation
Dim cur_emp As String   ''this will hold current value returned by the recordset object while scanning through tha table

Set rsd = db.OpenRecordset("details", dbOpenTable)  'pointing the recordset to the table
If rsd.RecordCount > 0 Then     'checking if there is any record exists in the table. if its found any record then
                                'the recordcount should be greater than zero.
    rsd.MoveFirst   'pointing 'rsd' to the first record in the 'table. this actually notifies that a in-scanning process
                    'through the table is about to begin.
    While Not rsd.EOF()     'looping through the table until the end of file is reached
        cur_emp = rsd!empname   'parsing current cell value of 'empname' to the string var. 'cur_emp'
        If UCase(cur_emp) = UCase(e_name) Then      'now checking whether 'cur_emp' and the parameter passed does have
            IsEmpExists = True                      'same value or not. if yes then employee already exists. so returns
            Exit Function                           'true. once it becomes true there is no need of scanning again.so
        End If                                      'exists the function.
        rsd.MoveNext
    Wend
    IsEmpExists = False     'if no matching is found between 'cur_emp' and 'e_name' the function returns false,means there
                            'is no such employee exists with same name in the table.so you can proceed to add it into the
                            'database
Else    'there is no existing record found, so returns false
    IsEmpExists = False
End If
End Function

''this is just a sub-routine to call all existing employee records from the database and add them to the listbox. this
''becomes very useful in case of side-by-side viewing. you can easily see that the validation is going all ok.
Public Sub LoadEmployees()
Dim rsx As Recordset

Set rsx = db.OpenRecordset("details", dbOpenTable)
List1.Clear

If rsx.RecordCount > 0 Then
    rsx.MoveFirst
    While Not rsx.EOF()
        With List1
            .AddItem rsx!empname
            .ListIndex = .ListCount - 1
        End With
        rsx.MoveNext
    Wend
    Exit Sub
End If
List1.AddItem "-=No Existing Employees=-"
End Sub

Option Explicit
 
 
Dim rs As ADODB.Recordset
Dim Conn As ADODB.Connection
Dim esql As String
 
''this is the core function which is responsible for the entire validation process to be executed. this function takes
''the employee name as an arguement that you wish to check whether already exists in the database or not. the function
''checks for its first occurance. if found then it returns true otherwise returns false. "true" means the employee
''does already exist and "false" means not.
 
 
Public Function IsEmpExists(ByVal e_name As String) As Boolean
Dim rsd As New ADODB.Recordset    ''declarigng a recordset object specially for this operation
Dim cur_emp As String   ''this will hold current value returned by the recordset object while scanning through tha table
 
Set rsd = New ADODB.Recordset  'pointing the recordset to the table
rsd.CursorType = adOpenStatic
rsd.CursorLocation = adUseClient
rsd.LockType = adLockPessimistic
rsd.Source = ("Select * From tblStudacct")
rsd.ActiveConnection = Conn
rsd.Open
 
 
 
If rsd.RecordCount > 0 Then     'checking if there is any record exists in the table. if its found any record then
                                'the recordcount should be greater than zero.
    rsd.MoveFirst   'pointing 'rsd' to the first record in the 'table. this actually notifies that a in-scanning process
                    'through the table is about to begin.
    While Not rsd.EOF()     'looping through the table until the end of file is reached
        cur_emp = rsd!Firstname   'parsing current cell value of 'empname' to the string var. 'cur_emp'
        If UCase(cur_emp) = UCase(e_name) Then      'now checking whether 'cur_emp' and the parameter passed does have
            IsEmpExists = True                      'same value or not. if yes then employee already exists. so returns
            Exit Function                           'true. once it becomes true there is no need of scanning again.so
        End If                                      'exists the function.
        rsd.MoveNext
    Wend
    IsEmpExists = False     'if no matching is found between 'cur_emp' and 'e_name' the function returns false,means there
                            'is no such employee exists with same name in the table.so you can proceed to add it into the
                            'database
Else    'there is no existing record found, so returns false
    IsEmpExists = False
End If
End Function
 
 
 
Private Sub Command1_Click()
If Trim(txtempname.Text) <> "" Then
    If IsEmpExists(Trim(txtempname.Text)) Then
        MsgBox "The employee " & Chr(34) & Trim(txtempname.Text) & Chr(34) & " already exists in the database." & vbCrLf & _
            "Please use a different name rather than this one.", vbInformation, "Duplicate Employee"
        txtempname.SelStart = 0
        txtempname.SelLength = Len(Trim(txtempname.Text))
        txtempname.SetFocus
    Else
        rs.MoveNext
        rs.AddNew
            rs!Firstname = Trim(txtempname.Text)
            rs!StudentNumber = 324324535  'just made this because my primary key is not auto number
 
            rs.Update
            Call LoadEmployees
            MsgBox "New employee created.", vbInformation, "Done"
            txtempname.Text = ""
            txtempname.SetFocus
            End If
Else
    MsgBox "The employee name should not be null.", vbCritical
    txtempname.SetFocus
End If
End Sub
 
Private Sub Form_Load()
 
''making connection to the database and opening the employee table to retrieve existing records
Set Conn = New ADODB.Connection
Set rs = New ADODB.Recordset
 
esql = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\MAINDB2.mdb" & ";Persist Security Info=False"
 
Conn.Open (esql), , , 0
 
 
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Source = ("Select * From tblStudacct")
rs.ActiveConnection = Conn
rs.Open
 
If rs.RecordCount > 0 Then
rs.MoveFirst
Else
    MsgBox "No employee details found." & vbCrLf & _
        "'Please add some records.", vbInformation
End If
''creating the list of existing employees
Call LoadEmployees
End Sub
 
 
Public Sub LoadEmployees()
Dim rsx As New Recordset
 
Set rsx = New ADODB.Recordset
rsx.CursorType = adOpenStatic
rsx.CursorLocation = adUseClient
rsx.LockType = adLockPessimistic
rsx.Source = ("Select * From tblStudacct")
rsx.ActiveConnection = Conn
rsx.Open
 
 
List1.Clear
 
If rsx.RecordCount > 0 Then
    rsx.MoveFirst
    While Not rsx.EOF()
        With List1
            .AddItem rsx!Firstname
            .ListIndex = .ListCount - 1
        End With
        rsx.MoveNext
    Wend
    Exit Sub
End If
List1.AddItem "-=No Existing Employees=-"
End Sub