Linked Questions

Popular Questions

I have one table and one query. Both have the same data field but table COLUMN names are equal to query's ROW name. I update table from query's row data using the following code successfully but it takes too much time to update as there are more than 50 columns name in the table for each employee-

Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM tblPayRollDataTEMP")
Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM qryEmpVerifySalary ")

Do Until rst1.EOF
    rst2.MoveFirst
    Do Until rst2.EOF                  
        For l = 0 To rst1.Fields.count - 1
            If rst1!EmpID = rst2!EmpID And rst1.Fields(l).Name = rst2!Head And rst1!PayBillID = TempVars!BillID Then
                With rst1
                    rst1.Edit
                    rst1.Fields(l).Value = rst2!Amount
                    rst1!totDeductions = DSum("Amount", "qryEmpVerifySalary", "[PayHeadType] = 'Deductions' AND [EmpID] = " & rst2!EmpID & "") + DLookup("NPS", "qryEmpPayEarning", "[EmpID] = " & rst2!EmpID & "")
                    rst1!totRecoveries = DSum("Amount", "qryEmpVerifySalary", "[PayHeadType] = 'Recoveries' AND [EmpID] = " & rst2!EmpID & "")
                    rst1!NetPayable = rst1!totEarnings - (Nz(rst1!totDeductions, 0) + Nz(rst1!totRecoveries, 0))
                    rst1.Update
                End With

            End If

        Next
        rst2.MoveNext
    Loop
    rst1.MoveNext
Loop

Set rst1 = Nothing
Set rst2 = Nothing

How to improve the performance of the code?

Related Questions