|
|
|
For small Businesses where number of transactions is less than 500,000 the Microsoft Access can be used as a very successful
Database Management System.
Microsoft Access is widely being used in payroll systems, equipment maintenance systems and office automation systems.
Assume that you want to develop a system to provide salary increments to employees.
Run the following codes to display GRADE ID, EMPLOYEE NO, and SALARY CODE etc.
As a first step create necessary tables as follows.
MDM_EMPLOYEES
EMPLOYEE_NO
EMPLOYEE_NAME
GRADE_ID
DESIGNATION_ID
MDM_DESIGNATION
DESIGNATION_ID
DESIGNATION
SALARY_CODE_ID
CUR_EMP_NO
‘Compile and run the following code to display information
‘Do the necessary declaration of variables.
Option Compare Database
Option Explicit
Dim db As Database
Dim rs As
Recordset
Dim rsDesig
As Recordset
Dim rsCurrent
As Recordset
Dim ENO As
Integer
Dim BGID As
Integer
Dim DID As Integer
Dim SALCODEID As Integer
‘Click event of command button
‘Command click event procedure is as follows
Private Sub START_Click()
Set db = CurrentDb
Set rs = db.OpenRecordset("MDM_EMPLOYEES")
Set rsCurrent = db.OpenRecordset("CUR_EMP_NO")
Set rsDesig = db.OpenRecordset("MDM_DESIGNATION")
ENO = InputBox("Give the EMPLOYEE No ")
rsCurrent.Edit
rsCurrent![EMPLOYEE_NO] = ENO
rsCurrent.Update
Do Until rs.EOF
If rs![EMPLOYEE_NO] = ENO Then
BGID = rs![BOARD_GRADE_ID]
DID = rs![DESIGNATION_ID]
End If
rs.MoveNext
MsgBox "EMPLOYEE_NO " & ENO, vbInformation
MsgBox "BOARD_GRADE_ID = " & BGID, vbInformation
MsgBox "DESIGNATION_ID = " & DID, vbInformation
rsCurrent.Edit
rsCurrent![EMPLOYEE_NO] = ENO
rsCurrent.Update
Do Until rsDesig.EOF
if rsDesig![DESIGNATION_ID] = DID Then
SALARYCODEID = rsDesig![SALARY_CODE_ID]
end If
rsDesig.MoveNext
MsgBox "SALARY_CODE_ID" & SALCODEID, vbInformation
End Sub