Code:
Private Sub Cmdvalidate_Click()
Dim PayrollManagerDb As Database
Dim strSQL As String
Dim SalaryExtendedRs As Recordset
Dim SalaryExtendedLoopRs As Recordset
Dim insertStrSql As String
' SalaryExtended field indices
Dim Se_EmployeedId_Idx As Integer
Dim Se_Gross_Idx As Integer
Dim Se_Pit_Idx As Integer
Dim Se_Act_Idx As Integer
Dim Se_PensionScheme_Idx As Integer
Dim Se_Crtv_Idx As Integer
Dim Se_Hlf_Idx As Integer
Dim Se_SalaryAdvance_Idx As Integer
Dim Se_LoanRepayment_Idx As Integer
Dim Se_FoodRepayment_Idx As Integer
Dim Se_CourtDeductions_Idx As Integer
Dim Se_HousingRepayment_Idx As Integer
Dim Se_MonthName_Idx As Integer
Dim Se_EstId_Idx As Integer
If IsNull(Me.Estid) Or IsNull(Me.payfrom) Or IsNull(Me.payto) Or IsNull(Me.monthname) Then
MsgBox "All the fields are mandatory. Please fill all of them before proceeding to carryout payment", vbOKOnly, "For your promt action"
ElseIf IsNull(DLookup("payID", "payment", "paymonth=" & Me.monthname & " AND Year(payfrom)=" & year(Me.payfrom) & " AND estID=" & Me.Estid)) Then
strSQL = "SELECT * FROM SalaryExtended WHERE (EstId=" & Me.Estid & " AND monthname=" & Me.monthname & ")" _
& " OR (monthname Is Null AND EstId=" & Me.Estid & ");"
'Print report of original data
Debug.Print strSQL
Set PayrollManagerDb = CurrentDb
Set SalaryExtendedRs = PayrollManagerDb.OpenRecordset(strSQL)
Se_EmployeeId_Idx = 0
Se_Gross_Idx = 1
Se_Pit_Idx = 2
Se_Act_Idx = 3
Se_PensionScheme_Idx = 4
Se_Crtv_Idx = 5
Se_Hlf_Idx = 6
Se_SalaryAdvance_Idx = 7
Se_LoanRepayment_Idx = 8
Se_FoodRepayment_Idx = 9
Se_CourtDeductions_Idx = 10
Se_HousingRepayment_Idx = 11
Se_MonthName_Idx = 12
Se_EstId_Idx = 13
With SalaryExtendedRs
Do While Not .EOF
insertStrSql = "INSERT INTO payment (employeeid,gross,pit,act,pensionscheme,crtv,hlf,salaryadvance,loanrepayment" _
& ",housingrepayment,foodrepayment,courtdeduction,paydate,payfrom,payto,doe,paymonth,estid)" _
& "VALUES (" _
& .Fields(Se_EmployeeId_Idx) & "," _
& .Fields(Se_Gross_Idx) & "," _
& Nz(.Fields(Se_Pit_Idx), "0") & "," _
& Nz(.Fields(Se_Act_Idx), "0") & "," _
& Nz(.Fields(Se_PensionScheme_Idx), "0") & "," _
& Nz(.Fields(Se_Crtv_Idx), "0") & "," _
& Nz(.Fields(Se_Hlf_Idx), "0") & "," _
& Nz(.Fields(Se_SalaryAdvance_Idx), "0") & "," _
& Nz(.Fields(Se_LoanRepayment_Idx), "0") & "," _
& Nz(.Fields(Se_HousingRepayment_Idx), "0") & "," _
& Nz(.Fields(Se_FoodRepayment_Idx), "0") & "," _
& Nz(.Fields(Se_CourtDeductions_Idx), "0") & "," _
& "#" & Me.paydate & "#," _
& "#" & Me.payfrom & "#," _
& "#" & Me.payto & "#," _
& "#" & Date & "#," _
& Me.monthname & "," _
& .Fields(Se_EstId_Idx) & ")"
PayrollManagerDb.Execute (insertStrSql)
'Debug.Print insertStrSql
.MoveNext
Loop
End With
MsgBox "Payment succesfully done", vbOKOnly, "PAYroll Informer"
Else
MsgBox "The payments for this month in this establishment have already been done.", vbOKOnly, "PAYroll Informer"
End If
End Sub