I’ve got a problem with certain fields on a data entry form that are taking too long to calculate. Users are reporting delays of 8 to 15 seconds whilst these calculations are happening which doesn’t sound that long but it’s happening for every record and they’ve to enter a lot of records each day.
The database is Access 2007 format. It’s a split database that user’s access over citrix.
On the data entry screen there’s a field where a machine number is selected which populates a machine start hours field (HoursStart).
It’s when the user tabs out of the HoursStart field the following code runs:
Is there anything wrong with this code that would be causing the delay?
The database is Access 2007 format. It’s a split database that user’s access over citrix.
On the data entry screen there’s a field where a machine number is selected which populates a machine start hours field (HoursStart).
It’s when the user tabs out of the HoursStart field the following code runs:
Code:
Private Sub HoursStart_LostFocus()
On Error Resume Next
Me.txtClockHours.Value = Me.HoursFinish.Value - Me.HoursStart.Value
On Error GoTo 0
Call calc_totalhours
Me.HoursFinish.SetFocus
End Sub
Private Sub calc_totalhours()
On Error GoTo Err_MyProc
Dim db As Database
Dim rst1 As DAO.Recordset
Dim SQLVar As String
Set db = CurrentDb()
SQLVar = "SELECT DISTINCTROW SUM (tblDOD.HoursFinish-tblDOD.HoursStart) AS Sum_ClockHours FROM tblJobs INNER JOIN " _
& "(tblDC INNER JOIN tblDOD ON tblDC.DiaryID = " _
& "tblDOD.DiaryID) ON tblJobs.JobID = tblDC.JobID " _
& "WHERE tblDOD.MachineNumber= " & Me.ctlMachineNumber.Value _
& " AND tblJobs.JobID = " & Form_frmSiteDataEntryMain.JobID & " AND tblDC.OperationDate <= #" _
& Format(Form_frmSiteDataEntryMain.OperationDate, "m/d/yy") & "# AND tblDOD.HoursStart <= " & IIf(IsNull(Me.HoursStart), 0, Me.HoursStart)
Set rst1 = db.OpenRecordset(SQLVar, dbOpenSnapshot)
Me.txtMachineTotalHours.Value = rst1!Sum_ClockHours
'Useful code here.
rst1.CLOSE 'Close what you opened.
Exit_MyProc:
Set rst1 = Nothing 'Deassign all objects.
Set db = Nothing
Exit Sub
Err_MyProc:
'Error handler here.
Resume Exit_MyProc
End Sub
Is there anything wrong with this code that would be causing the delay?