• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Adding Employees to a database(ms Access) though ODBC SQL staments are used. Some components like j

Adding Employees to a database(ms Access) though ODBC SQL staments are used. Some components like j

Adding Employees to a database(ms Access) though ODBC SQL staments are used. Some components like janus bar/ button, crystal reports has be

API Declarations


'connecting the table to the database
Dim RsRecords As New ADODB.Recordset
'variable datatypes
Dim mike As Long
Dim X As Long
Dim Y As Long




Rate Adding Employees to a database(ms Access) though ODBC SQL staments are used. Some components like j

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

Private Sub CmbEmployeeCode_Click()
    'This object's lists is reflected on to the other combo box
    CmbEmployeeName.ListIndex = CmbEmployeeCode.ListIndex
    GetEmployeeDetails
End Sub
Private Sub GetEmployeeDetails()
On Error GoTo mic
    'gets and shows all the details contained on the form using the sql statement and displayed on the combobox
    Set RsRecords = Nothing
    RsRecords.Open ("SELECT EmployeeName,EmployeeNumber,Address,TelephoneNumber,IDNumber,EmailAddress,Password,Notes FROM Employees WHERE EmployeeNumber = " & CmbEmployeeCode.Text & ""), Database, adOpenKeyset, adLockOptimistic

    If RsRecords.BOF = False And RsRecords.EOF = False Then
        CmbEmployeeName.Text = RsRecords!EmployeeName
        TxtEmployeeNumber.Text = RsRecords!EmployeeNumber
        TxtIDNumber.Text = RsRecords!IDNumber
        TxtTelephone.Text = RsRecords!TelephoneNumber
        TxtAddress.Text = RsRecords!Address
        TxtEmailAddress.Text = RsRecords!EmailAddress
        TxtPassword.Text = RsRecords!Password
        TxtNotes.Text = RsRecords!Notes
    End If
Exit Sub
mic:
    MsgBox ("Error"), vbCritical + vbYesNo
End Sub

Private Sub CmbEmployeeName_Click()
    'on the otherhand this is equivalent to the combobox which show the employee number
    CmbEmployeeCode.ListIndex = CmbEmployeeName.ListIndex
End Sub

Private Sub CmbEmployeeName_KeyPress(KeyAscii As Integer)
    KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

Private Sub CmdAdd_Click()
On Error GoTo Err
    'The Add button will change to a save button once clicked on and also the close button will change to a cancel button
    If CmdAdd.Caption = "Add" Then
        'With ".Enable" one is not able to use this objects whenever one has clicked on add
        CmdEdit.Enabled = False
        CmdDelete.Enabled = False
        CmdFind.Enabled = False
        CmdPrint.Enabled = False

        CmdAdd.Caption = "Save"
        CmdClose.Caption = "Cancel"
        
        'This will make sure all the required textboxes are cleared or become empty once you click on add to add new records
        ClearTextBoxes
        'This will Unlock the textboxes to be able to input records on the textboxes since they are locked inorder for changes not to take place
        UnlockTextBoxes
        'To generate a new number will increase the number of records on the database by 1
        GenerateNewNumber
        'The pointer is taken to the combobox with employees' names
        CmbEmployeeName.SetFocus
    Else
        If DoValidations = False Then MsgBox ("Some fields are not being entered, please enter them before saving"), vbExclamation + vbOKOnly: CmbEmployeeName.SetFocus: Exit Sub
        Database.Execute ("INSERT INTO Employees (EmployeeNumber,EmployeeName,IDNumber,Address,TelephoneNumber,EmailAddress,Password,Notes) VALUES (" & TxtEmployeeNumber.Text & ",'" & CmbEmployeeName.Text & "', '" & TxtIDNumber.Text & "', '" & TxtAddress.Text & "', '" & TxtTelephone.Text & "', '" & TxtEmailAddress.Text & "','" & TxtPassword.Text & "', '" & TxtNotes.Text & "') ")
        MsgBox ("" & CmbEmployeeName.Text & " was successfully saved into The Employees Record"), vbInformation + vbOKOnly, "Employees Record"

        CmdAdd.Caption = "Add"
        CmdClose.Caption = "Close"
        
          'Activate the buttons
        CmdEdit.Enabled = True
        CmdDelete.Enabled = True
        CmdFind.Enabled = True
        CmdPrint.Enabled = True
        CmdAdd.Enabled = True

        'This displays the record in the database and loads them on the employeeform
        DisplayRecords
        'This loads the employees on the form and as you browse through you'll see all the employees on the database
        LoadGridEmployees
        LoadEmployee
        LockTextBoxes
    End If
Exit Sub
Err:
    MsgBox ("An Error manupilating the records"), vbCritical + vbOKCancel
End Sub

Private Sub UnlockTextBoxes()
On Error GoTo mic
    'Lock makes it impossible for one to make changes on the details without first going to the edit button to make changes
    TxtAddress.Locked = False
    CmbEmployeeName.Locked = False
    TxtEmployeeNumber.Locked = False
    TxtTelephone.Locked = False
    TxtIDNumber.Locked = False
    TxtEmailAddress.Locked = False
    TxtPassword.Locked = False
    TxtNotes.Locked = False
Exit Sub
mic:
    MsgBox ("Error"), vbCritical + vbYesNo
End Sub
Private Sub CmdClose_Click()
On Error GoTo Err
    'Alternatively the close button changes to cancel and then save button changes back to add
    If CmdClose.Caption = "Cancel" Then
        'Activate buttons on clicking (cancel)
        CmdEdit.Enabled = True
        CmdDelete.Enabled = True
        CmdFind.Enabled = True
        CmdPrint.Enabled = True
        CmdAdd.Enabled = True

        CmdAdd.Caption = "Add"
        CmdClose.Caption = "Close"
        CmdEdit.Caption = "Edit"
        
        DisplayRecords
        LoadEmployee
        LoadGridEmployees
        LockTextBoxes
        
    Else
        'closes or ends the form
        Unload Me
    End If
Exit Sub
Err:
    MsgBox ("An Error manupilating the records"), vbCritical + vbOKCancel
End Sub

Private Sub CmdDelete_Click()
On Error GoTo Err
    If MsgBox("Are you sure you want to Permanently Delete " & CmbEmployeeName.Text & " from the Database?", vbCritical + vbYesNo) = vbNo Then Exit Sub
    'sql statement that deletes the employee details on the database
    Database.Execute ("DELETE FROM Employees WHERE EmployeeNumber = " & CmbEmployeeCode.Text & "")
    LoadEmployee
Exit Sub
Err:
    MsgBox ("An Error occured Deleting the users from the record"), vbCritical + vbOKCancel
End Sub

Private Sub CmdEdit_Click()
On Error GoTo mic
    'Edit button changes to save and makes the close button also to change to cancel
    If CmdEdit.Caption = "Edit" Then
        'Deactivate buttons so that no other task is to be carried out when the user is editing
        CmdAdd.Enabled = False
        CmdDelete.Enabled = False
        CmdFind.Enabled = False
        CmdPrint.Enabled = False

        CmdEdit.Caption = "Save"
        CmdClose.Caption = "Cancel"
        
        'Textboxes are able be Edited once unlocked
        UnlockTextBoxes
        
    Else
        CmdEdit.Caption = "Edit"
        CmdClose.Caption = "Close"
        'sql uses the update statement to make changes to the database after editing
        Database.Execute ("UPDATE Employees SET EmployeeName = '" & CmbEmployeeName.Text & "', IDNumber = '" & TxtIDNumber.Text & "', Address = '" & TxtAddress.Text & "', TelephoneNumber = '" & TxtTelephone.Text & "', EmailAddress = '" & TxtEmailAddress.Text & "', Password = '" & TxtPassword.Text & "', Notes= '" & TxtNotes.Text & "' WHERE EmployeeNumber = " & TxtEmployeeNumber.Text & " ")
        MsgBox (" " & CmbEmployeeName & "'s Information was successfully Edited and Saved into the Employees Record"), vbInformation + vbOKOnly, "Editing Employees Records"
    
    'Activate the buttons
    CmdAdd.Enabled = True
    CmdDelete.Enabled = True
    CmdFind.Enabled = True
    CmdPrint.Enabled = True
    
    LockTextBoxes
    LoadGridEmployees
    GetEmployeeDetails
    
    End If
Exit Sub
mic:
    MsgBox ("Error"), vbCritical + vbYesNo
End Sub

Private Sub CmdFind_Click()
On Error GoTo mike
    'determine the form you're in
    FormName = FrmEmployees.Name
    'initializes the form that searches form specific categories
    Load FrmSearchScreen
    'Shows the search screen to search
    FrmSearchScreen.Show 1
Exit Sub
mike:
    MsgBox ("Not covered in this Version"), vbCritical + vbYesNo
End Sub

Private Sub CmdPrint_Click()
On Error GoTo mike
'Report is connected to the database thus the database name "MIKE" is called
CrystalReportPrint.Connect = "MIKE"
    'The report path is located by calling it's name and the specific report type for the particular form ie employee
    CrystalReportPrint.ReportFileName = App.Path & "\Reports\Employees.rpt"
    'This shows where the output is displayed in this case it'll be displayed on the monitor and not to the printer directly
    CrystalReportPrint.Destination = crptToWindow
    CrystalReportPrint.Action = 0
Exit Sub
mike:
    MsgBox ("Output error, configure the report path on the database"), vbCritical + vbYesNo
End Sub

Private Sub Form_Load()
On Error GoTo Err
    'Sets the form at the centre of the screen
    CentreScreen Me
    
    ClearTextBoxes
    LockTextBoxes
    DisplayRecords

    LoadEmployee
    DisplayRecords
    LoadGridEmployees
    LockTextBoxes
Exit Sub
Err:
    MsgBox ("Problem Loading the information from the database"), vbCritical + vbOKCancel
End Sub

Private Sub LockTextBoxes()
    'No changes can be made to the textboxes
    TxtAddress.Locked = True
    CmbEmployeeName.Locked = True
    TxtEmployeeNumber.Locked = True
    TxtTelephone.Locked = True
    TxtIDNumber.Locked = True
    TxtPassword.Locked = True
    TxtEmailAddress.Locked = True
    TxtNotes.Locked = True
End Sub
Private Sub ClearTextBoxes()
   'Empty the textboxes
    TxtAddress.Text = ""
    CmbEmployeeName.Text = ""
    TxtEmployeeNumber.Text = ""
    TxtTelephone.Text = ""
    TxtIDNumber.Text = ""
    TxtEmailAddress.Text = ""
    TxtPassword.Text = ""
    TxtNotes.Text = ""
End Sub

Private Sub DisplayRecords()
On Error GoTo mic
    If CmbEmployeeCode = "" Then Exit Sub
    Set RsRecords = Nothing
    RsRecords.Open ("SELECT EmployeeName,EmployeeNumber,IDNumber,TelephoneNumber,Address,EmailAddress,Password,Notes FROM Employees"), Database, adOpenKeyset, adLockOptimistic

    If RsRecords.BOF = False And RsRecords.EOF = False Then
        RsRecords.MoveFirst
        TxtAddress.Text = RsRecords!Address
        CmbEmployeeName.Text = RsRecords!EmployeeName
        TxtEmployeeNumber.Text = RsRecords!EmployeeNumber
        TxtTelephone.Text = RsRecords!TelephoneNumber
        TxtIDNumber.Text = RsRecords!IDNumber
        TxtEmailAddress.Text = RsRecords!EmailAddress
        TxtPassword.Text = RsRecords!Password
        TxtNotes.Text = RsRecords!Notes
    End If
Exit Sub
mic:
    MsgBox ("Error"), vbCritical + vbYesNo
End Sub
Private Sub GenerateNewNumber()
On Error GoTo mic
    Set RsRecords = Nothing
        RsRecords.Open ("SELECT EmployeeNumber FROM Employees ORDER BY VAL(EmployeeNumber) DESC"), Database, adOpenKeyset, adLockOptimistic

    If RsRecords.BOF = False And RsRecords.EOF = False Then
        RsRecords.MoveFirst
        TxtEmployeeNumber.Text = RsRecords!EmployeeNumber + 1
    Else
        TxtEmployeeNumber.Text = 1
    End If
Exit Sub
mic:
    MsgBox ("Error"), vbCritical + vbYesNo
End Sub
Private Sub TxtEmailAddress_LostFocus()
    TxtPassword.Enabled = True
End Sub

Private Sub TxtEmployeeNumber_Validate(Cancel As Boolean)
On Error GoTo mic
    Set RsRecords = Nothing
    RsRecords.Open ("SELECT EmployeeName,EmployeeNumber,IDNumber,TelephoneNumber,Address,EmailAddress,Password,Notes FROM Employees WHERE EmployeeNumber = " & TxtEmployeeNumber.Text & " "), Database, adOpenKeyset, adLockOptimistic

    If RsRecords.BOF = False And RsRecords.EOF = False Then

        TxtAddress.Text = RsRecords!Address
        CmbEmployeeName.Text = RsRecords!EmployeeName
        TxtEmployeeNumber.Text = RsRecords!EmployeeNumber
        TxtTelephone.Text = RsRecords!TelephoneNumber
        TxtIDNumber.Text = RsRecords!IDNumber
        TxtEmailAddress.Text = RsRecords!EmailAddress
        TxtPassword.Text = RsRecords!Password
        TxtNotes.Text = RsRecords!Notes
    Else
        MsgBox ("This record " & TxtEmployeeNumber.Text & " Does not exist!"), vbExclamation + vbOKOnly
    End If
Exit Sub
mic:
    MsgBox ("Error"), vbCritical + vbYesNo
End Sub

Private Function DoValidations() As Boolean
    If TxtAddress.Text = "" Then DoValidations = False: Exit Function
    If CmbEmployeeName.Text = "" Then DoValidations = False: Exit Function
    If TxtEmployeeNumber.Text = "" Then DoValidations = False: Exit Function
    If TxtTelephone.Text = "" Then DoValidations = False: Exit Function
    If TxtIDNumber.Text = "" Then DoValidations = False: Exit Function
    If TxtPassword.Text = "" Then DoValidations = False: Exit Function
    If TxtEmailAddress.Text = "" Then DoValidations = False: Exit Function
    DoValidations = True
End Function

Private Sub LoadEmployee()
On Error GoTo ErrHandler
    Set RsRecords = Nothing
    RsRecords.Open ("SELECT EmployeeName, EmployeeNumber FROM Employees ORDER BY EmployeeName ASC"), Database, adOpenKeyset, adLockOptimistic
    
    If RsRecords.BOF = False And RsRecords.EOF = False Then
        CmbEmployeeCode.Clear
        CmbEmployeeName.Clear
        
        For mike = 0 To RsRecords.RecordCount - 1
            CmbEmployeeName.AddItem RsRecords!EmployeeName
            CmbEmployeeCode.AddItem RsRecords!EmployeeNumber
            
            RsRecords.MoveNext
        Next mike
        CmbEmployeeCode.ListIndex = 0
    Else
        ClearTextBoxes
    End If
Exit Sub
ErrHandler:
    MsgBox ("Error Loading the Employees, seek programmer's guide"), vbCritical + vbOKOnly
End Sub
Private Sub LoadGridEmployees()
On Error GoTo mic
    Set RsRecords = Nothing
    RsRecords.Open ("SELECT EmployeeName,EmployeeNumber,Address,TelephoneNumber,IDNumber,Notes,EmailAddress,Password FROM Employees ORDER BY EmployeeName ASC"), Database, adOpenKeyset, adLockOptimistic

    VsEmployees.FormatString = "Employee Name|Employee Number|Address|Telephone#|ID#|Notes|Email|Password"
    VsEmployees.ColWidth(0) = 1800
    VsEmployees.ColWidth(1) = 0
    VsEmployees.ColWidth(2) = 1900
    VsEmployees.ColWidth(4) = 700
    VsEmployees.ColWidth(5) = 0
    VsEmployees.ColWidth(6) = 1500
    VsEmployees.ColWidth(7) = 0
    
    If RsRecords.BOF = False And RsRecords.EOF = False Then
        'gridname (vsemployees)that displays the list of employees and their details(in rows) is equal to the table(dim as RsRecords) record number of all the employee number in database and it'll add 1 row that access usually adds for another input just incase
        VsEmployees.Rows = RsRecords.RecordCount + 1
        'Shows the Columns ie the fields that are there in the table
        VsEmployees.Cols = RsRecords.Fields.Count
        'x represents the rows ie the details of employees on the table and subtracts the one added row that is put by access by default
        For X = 0 To RsRecords.RecordCount - 1
            'y represents the columns(fields) indicated on the table and subtracts the one(title column to show)
            For Y = 0 To RsRecords.Fields.Count - 1
                'the grid shows both the y and x (+ the additional one row) grids which is equalized by the fields(y) value
                VsEmployees.TextMatrix(X + 1, Y) = RsRecords.Fields(Y).Value
            Next Y
            'table moves to the next row(x)
            RsRecords.MoveNext
        Next X
    End If
Exit Sub
mic:
    MsgBox ("Error"), vbCritical + vbYesNo
End Sub


Private Sub TxtNotes_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then CmdAdd_Click
End Sub

Private Sub TxtPassword_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then CmdAdd_Click
End Sub

Private Sub VsEmployees_Click()
On Error GoTo mic
    VsEmployees.Col = 1
    TxtEmployeeNumber.Text = VsEmployees.Text
    TxtEmployeeNumber_Validate False
    
    CmdEdit.Caption = "Edit"
    CmdClose.Caption = "Close"
    CmdAdd.Caption = "Add"
        
    CmdAdd.Enabled = True
    CmdEdit.Enabled = True
    CmdDelete.Enabled = True
    CmdFind.Enabled = True
    CmdPrint.Enabled = True
    
    LockTextBoxes
Exit Sub
mic:
    MsgBox ("Error"), vbCritical + vbYesNo
End Sub

Private Sub VsEmployees_RowColChange()
    VsEmployees.Col = 1
    TxtEmployeeNumber.Text = VsEmployees.Text
    TxtEmployeeNumber_Validate False
End Sub