How to calculate the increment percent of the salary

[shivanee]

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

[mshmyob]

First fix up your code so we can read it.

Move your 3 DIM statements that are after your rst.open command to the top. All variable dimension should be done before code.

Indent all your code so we can figure out where the END, D0 LOOP etc. statements start and end.

Put your code in the code tags.

cheers,