Hi,
I have the following function, which generates an 8 digit closure code which is issued to engineers when they close down a job.
Before it issues the code, it checks to see if that code has already been issued previously or not.
I have it generating a random code, so that sequential numbers aren't used and therefore the engineers can't take a guess as the closure code.
It works absolutely fine, but the first job that is closed down when the system is first used, is really slow (20 - 30 seconds) to check the if the code has been used previously, before moving onto the rest of the code. Once it has done this once it is nice and quick after that until you close and reopen the system.
What could I do to speed this up? Would doing a rs.RecordCount be quicker that a DCount?
Thanks
I have the following function, which generates an 8 digit closure code which is issued to engineers when they close down a job.
Before it issues the code, it checks to see if that code has already been issued previously or not.
I have it generating a random code, so that sequential numbers aren't used and therefore the engineers can't take a guess as the closure code.
It works absolutely fine, but the first job that is closed down when the system is first used, is really slow (20 - 30 seconds) to check the if the code has been used previously, before moving onto the rest of the code. Once it has done this once it is nice and quick after that until you close and reopen the system.
What could I do to speed this up? Would doing a rs.RecordCount be quicker that a DCount?
Thanks
Code:
Function ClosureCodeGen()
Dim Code1 As Integer
Dim Code2 As Integer
Dim code3 As Integer
Dim code4 As Integer
Dim code5 As Integer
Dim code6 As Integer
Dim code7 As Integer
Dim code8 As Integer
LabelA: Code1 = Int((9 - 0) * Rnd + 1)
Code2 = Int((9 - 0) * Rnd + 1)
code3 = Int((9 - 0) * Rnd + 1)
code4 = Int((9 - 0) * Rnd + 1)
code5 = Int((9 - 0) * Rnd + 1)
code6 = Int((9 - 0) * Rnd + 1)
code7 = Int((9 - 0) * Rnd + 1)
code8 = Int((9 - 0) * Rnd + 1)
Code = Code1 & Code2 & code3 & code4 & code5 & code6 & code7 & code8
If DCount("[ID]", "tblClosureCodes", "[ClosureCode] = " & Code & "") = 0 Then
DoCmd.RunSQL ("INSERT INTO tblClosureCodes (ClosureCode) VALUES (" & Code & ")")
Else
GoTo LabelA
End If
'ClosureCodeGen = Code
TempVars("tmpClosureCode") = Code
End Function