Slow data entry form

[Guest]

I have a payroll data entry form that compares the record just entered to
processed records in a history file to check for duplication. If the employee
number, budget code and work date are the same as a record in history, it
warns them of a duplication and tells them which batch to check to verify
that it isn't a double payment. (Some duplications are valid.) My code may
not be elegant but it works. The problem is that the history file is now over
70000 records and it takes a full five seconds to do the validation for each
record. This lag time is a real pain for my users. They were originally
linked to the history file on the server but I thought it would speed things
up if the file was on their machine so I changed that . They now download a
copy of the file to their front-end on entry.
This is the code that I have in the BeforeUpdate event of my data entry form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varkey1 As Variant
Dim varkey2 As Variant
Dim varkey3 As Variant
Dim varkey4 As Variant
Dim varkey5 As Variant
Dim varkey6 As Variant
Dim Ans1 As Integer
Dim Ans2 As Integer
' Prompt for missing date
If IsNull(STARTDATE) Then
MsgBox "Please enter work date."
STARTDATE.SetFocus
End If
' Check for proper date

If (STARTDATE) > (ENDDATE) Then
MsgBox "Your Work End Date is prior to your Work Start Date. Please
correct."
STARTDATE.SetFocus
End If

' Check for duplicate in history
varkey1 = (DLookup("[tblhistory]![Budget Code]", "[tblHistory]", "
((([tblHistory]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL NUMBER]) and
(Forms![newSBPEARN1]![StartDate] = [tblHistory]![startdate])) or
(([tblHistory]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL NUMBER]) and
(Forms![newSBPEARN1]![StartDate] between [tblHistory]![startdate] and
[tblHistory]![enddate]))) "))
varkey2 = (DLookup("[tblhistory]![remote batch number]",
"[tblHistory]", " ((([tblHistory]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL
NUMBER]) and (Forms![newSBPEARN1]![StartDate] = [tblHistory]![startdate]))
or (([tblHistory]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL NUMBER]) and
(Forms![newSBPEARN1]![StartDate] between [tblHistory]![startdate] and
[tblHistory]![enddate]))) "))
varkey5 = (DLookup("[tblhistory]![fund number]", "[tblHistory]", "
((([tblHistory]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL NUMBER]) and
(Forms![newSBPEARN1]![StartDate] = [tblHistory]![startdate])) or
(([tblHistory]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL NUMBER]) and
(Forms![newSBPEARN1]![StartDate] between [tblHistory]![startdate] and
[tblHistory]![enddate]))) "))
[POSSIBLEDUPE] = varkey2
[OldFund] = varkey5
If (varkey1) = BUDGET_CODE And Not IsNull(varkey2) Then
Ans1 = MsgBox("This employee already has a payment in the history
file for this work date. Check batch number " & varkey2, vbRetryCancel,
"Invalid Date")
If Ans1 = vbRetry Then
STARTDATE.SetFocus
If Ans1 = vbCancel Then
Exit Sub
End If
End If

Else
' Check for duplicate in current data
varkey3 = (DLookup("[tblEarnings]![Budget Code]", "[tblEarnings]", "
((([tblEarnings]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL NUMBER]) and
(Forms![newSBPEARN1]![StartDate] = [tblEarnings]![startdate])) or
(([tblEarnings]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL NUMBER]) and
(Forms![newSBPEARN1]![StartDate] between [tblEarnings]![startdate] and
[tblEarnings]![enddate]))) "))
varkey4 = (DLookup("[tblEarnings]![remote batch number]",
"[tblEarnings]", " ((([tblEarnings]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL
NUMBER]) and (Forms![newSBPEARN1]![StartDate] = [tblEarnings]![startdate]))
or (([tblEarnings]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL NUMBER]) and
(Forms![newSBPEARN1]![StartDate] between [tblEarnings]![startdate] and
[tblEarnings]![enddate]))) "))
varkey6 = (DLookup("[tblEarnings]![Fund Number]", "[tblEarnings]", "
((([tblEarnings]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL NUMBER]) and
(Forms![newSBPEARN1]![StartDate] = [tblEarnings]![startdate])) or
(([tblEarnings]![EMPL NUMBER] = Forms![newSBPEARN1]![EMPL NUMBER]) and
(Forms![newSBPEARN1]![StartDate] between [tblEarnings]![startdate] and
[tblEarnings]![enddate]))) "))
[POSSIBLEDUPE] = varkey4
[OldFund] = varkey6
If (varkey3) = BUDGET_CODE And Not IsNull(varkey4) Then
Ans2 = MsgBox("This employee already has a payment in your current
data for this work date. Check batch number " & varkey4, vbRetryCancel,
"Invalid Date")
If Ans2 = vbRetry Then
STARTDATE.SetFocus
If Ans2 = vbCancel Then
Exit Sub
End If
End If
End If
End If


End Sub


Like I said it may not be elegant but it works... The history file is
indexed by employee number, start date and end date. I'm sure all those
dLookups are slowing things down but I don't know enough to get around them.
Any ideas for speeding this thing up?
TIA,
Lynne

 

[onedaywhen]

My code may
not be elegant but it works. The problem is that the history file is now over
70000 records and it takes a full five seconds to do the validation for each
record. This lag time is a real pain for my users.

Any ideas for speeding this thing up?

Here's an alternative approach:

1) Create database constraints (PRIMARY KEY, UNIQUE, validation
rule/CHECK, etc) with meaningful names to prevent invalid data from
entering the database. You should of course have such constraints in
place anyhow (see
http://www.dbazine.com/ofinterest/oi-articles/celko25).

2) Attempt to enter data into the database (possibly using a
transaction to be able to rollback partial operations etc).

3) Handle any error, using the meaningful constraint name contained in
the error description to determine the nature of the failure.

The idea is that a database round trip would be faster than your
existing five second front end validation code.

For example, rather than querying the database to determine whether the
forthcoming INSERT *would* fall foul of the PRIMARY KEY, instead try
the INSERT to see if it *does* fall foul of the PRIMARY KEY.

Jamie.

--