435,037 Members | 2,065 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,037 IT Pros & Developers. It's quick & easy.

How to calculate the increment percent of the salary

 P: 1 I am using 2 froms i.e salaryinfo is the main form, and salaryincrement is the subform. By selecting the empno i hv to enter his joining salary i.e.totalctc . If there is any increment then i hv to enter the revisedctc in the subform because in a company there is 2 increments per year.I used this code but i am getting error i.e when i finished the percentage calculation for 1st increment it is giving correctly, after that it has to close the form.like............ Private Sub IncrementPercent_LostFocus() Dim temp As String Dim Emp As String Dim tctc As String Set conn = CurrentProject.Connection Set rst = New ADODB.Recordset Set RST1 = New ADODB.Recordset Set RST2 = New ADODB.Recordset Set rs = New ADODB.Recordset With rs Set .ActiveConnection = conn .Source = "SELECT Distinct EmpNo,TotalCTC FROM SalaryInfo" .Open End With If Not rs.EOF Then rs.MoveFirst Do Until rs.EOF tctc = rs.Fields("TotalCTC") Emp = rs.Fields("Empno") rst.Open "select * from SalaryIncrement where EmpNo='" & Emp & "'", conn Dim CSno As String Dim PSno As String Dim Percent As String If Not rst.EOF Then rst.MoveFirst Do Until rst.EOF If DCount(rst.Fields("SId"), "SalaryIncrement", "[EmpNo]='" & Emp & "'") = 0 Then Exit Sub ElseIf DCount(rst.Fields("SId"), "SalaryIncrement", "[EmpNo]='" & Emp & "'") = 1 Then 'PSno = rst.Fields("SId") RST1.Open "select RevisedCTC from SalaryIncrement where SId=" & PSno & " andEmpNo='" & Emp & "'", conn 'RST1.Open "select Max(RevisedCTC) from SalaryIncrement where EmpNo='" & Emp & "' group by EmpNo,Year,Month,RevisedCTC order by EmpNo,RevisedCTC,Year,Month", conn temp = (RST1.Fields("RevisedCTC") - tctc) / tctc conn.Execute "update SalaryIncrement set IncrementPercent='" & temp & "' where SId=" & PSno & " and EmpNo='" & Emp & "'" RST1.Close ElseIf DCount(rst.Fields("SId"), "SalaryIncrement", "[EmpNo]='" & Emp & "'") > 1 Then PSno = rst.Fields("SId") CSno = PSno + 1 RST1.Open "select RevisedCTC from SalaryIncrement where SId=" & PSno & " and EmpNo='" & Emp & "'", conn RST2.Open "select RevisedCTC from SalaryIncrement where SId=" & CSno & " and EmpNo='" & Emp & "'", conn temp = (RST1.Fields("RevisedCTC") - RST2.Fields("RevisedCTC")) / RST2.Fields("RevisedCTC") conn.Execute "update SalaryIncrement set IncrementPercent='" & temp & "' where SId=" & PSno & " and EmpNo='" & Emp & "'" RST1.Close RST2.Close MsgBox temp End If rst.MoveNext Loop End If rst.Close rs.MoveNext Loop End If rs.Close conn.Close End Sub Nov 4 '08 #1 