by Michael Mwaura (2 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Tue 29th April 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
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
VBcoders
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
No comments have been posted about Adding Employees to a database(ms Access) though ODBC SQL staments are used. Some components like j. Why not be the first to post a comment about Adding Employees to a database(ms Access) though ODBC SQL staments are used. Some components like j.
