Help with module

[Randy]

Access 2000...I downloaded this module which will find gaps (Missing
sequential numbers) from my "CompletedCertificates" tbl and insert them into
my "Gaps" tbl. I would like to add another field [EmployeeId] from my
"CompletedCertificates" tbl into the "Gaps" tbl.. This way I will know
which employee is missing a [CertNumber]...Thanks...Randy

Function FindGaps()
Dim MyDB As DAO.Database
Dim mytbl As DAO.Recordset
Dim mytbl2 As DAO.Recordset
Dim lastnum As Long
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE GAPS.* FROM GAPS;"
DoCmd.SetWarnings True
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set mytbl = MyDB.OpenRecordset("SELECT * FROM CompletedCertificates ORDER BY
CertNumber")
Set mytbl2 = MyDB.OpenRecordset("GAPS")
With mytbl
.MoveFirst
lastnum = !CertNumber
Do Until .EOF
If !CertNumber - lastnum > 1 Then
lastnum = lastnum + 1
Do
With mytbl2
.AddNew
!CertNumber = lastnum
.Update
lastnum = lastnum + 1
If mytbl!CertNumber - lastnum = 0 Then
Exit Do
End If
End With
Loop
.MoveNext
Else
lastnum = !CertNumber
.MoveNext
End If
Loop
End With
mytbl.Close
mytbl2.Close
Set mytbl = Nothing
Set mytbl2 = Nothing
Set MyDB = Nothing
End Function

 

[John Vinson]

Access 2000...I downloaded this module which will find gaps (Missing
sequential numbers) from my "CompletedCertificates" tbl and insert them into
my "Gaps" tbl. I would like to add another field [EmployeeId] from my
"CompletedCertificates" tbl into the "Gaps" tbl.. This way I will know
which employee is missing a [CertNumber]...Thanks...Randy

I'm confused. Where would the EmployeeID be found? There is *NO*
record there; whose record isn't it?

Example: you have CompletedCertificates

CertNumber EmployeeID
1 83
2 52
3 118
5 47


Which EmployeeID should be put into the Gaps table?

Am I misunderstanding your question?

John W. Vinson[MVP]

 

[Randy]

The [EmloyeeId] is in the "CompletedCertificates" tbl and also in the
"CheckedOutCertificates" tbl. Each [EmployeeID] field is populated with the
appropriate number. I would like the "gaps" tbl not only identified with
the gaps in the numbers but also the [EmployeeId] that is associated with
the [Gap]...Thanks..Randy

Access 2000...I downloaded this module which will find gaps (Missing
sequential numbers) from my "CompletedCertificates" tbl and insert them
into
my "Gaps" tbl. I would like to add another field [EmployeeId] from my
"CompletedCertificates" tbl into the "Gaps" tbl.. This way I will know
which employee is missing a [CertNumber]...Thanks...Randy

I'm confused. Where would the EmployeeID be found? There is *NO*
record there; whose record isn't it?

Example: you have CompletedCertificates

CertNumber EmployeeID
1 83
2 52
3 118
5 47


Which EmployeeID should be put into the Gaps table?

Am I misunderstanding your question?

John W. Vinson[MVP]