GpA3vLX8

1Public Sub debug_GetMissingSeqentialIDs()
2    Call GetMissingSeqentialIDs("CHECKS", "CheckNum", "RESULTS")
3End Sub
4
5Public Sub GetMissingSeqentialIDs( _
6                                    ByVal SourceTable As String, _
7                                    ByVal SequenceFieldName As String, _
8                                    ByVal ResultsTable As String)
9                                    
10    '===========================================
11
12    'Svet Dimov | FinanceDev.com LLC | May 2018
13
14    '===========================================    
15
16On Error GoTo ERROR_PROC
17    
18    'Begin Log Timer
19    Dim vStartTime As Variant: vStartTime = VBA.Timer
20    'End Log Timer
21    
22    Dim sSQL As String
23    Dim lngMin As Long
24    Dim lngMax As Long
25    Dim lngCounter As Long
26    
27    'Create temp table, drop results tables if it exists
28    On Error Resume Next
29        sSQL = "CREATE TABLE [#TEMP_ALL_IDS]" & VBA.vbNewLine & _
30               "(" & SequenceFieldName & " INT)"
31        Call CurrentDb.Execute(sSQL, dbFailOnError)
32        sSQL = "DROP TABLE [" & ResultsTable & "]"
33        Call CurrentDb.Execute(sSQL, dbFailOnError)
34    On Error GoTo 0
35    
36    'Clear table contents of temp table
37    sSQL = "DELETE * FROM [#TEMP_ALL_IDS]"
38    Call CurrentDb.Execute(sSQL, dbFailOnError)
39    
40    'Find MIN and MAX
41    sSQL = "SELECT" & VBA.vbNewLine & _
42           "MIN([" & SequenceFieldName & "])" & VBA.vbNewLine & _
43           ",MAX([" & SequenceFieldName & "])" & VBA.vbNewLine & _
44           "FROM [" & SourceTable & "]" & VBA.vbNewLine & _
45           "WHERE [" & SequenceFieldName & "] IS NOT NULL"
46    lngMin = CurrentDb.OpenRecordset(sSQL).Fields(0).Value
47    lngMax = CurrentDb.OpenRecordset(sSQL).Fields(1).Value
48    
49    'Load all IDs to temo table
50    For lngCounter = lngMin To lngMax
51        sSQL = "INSERT INTO [#TEMP_ALL_IDS]" & VBA.vbNewLine & _
52               "(" & SequenceFieldName & ") VALUES (" & lngCounter & ")"
53        Call CurrentDb.Execute(sSQL, dbFailOnError)
54    Next lngCounter
55    
56    'Get missing SEQ_NOs and load into results table using SQL LEFT JOIN
57    sSQL = _
58        "SELECT [A].[" & SequenceFieldName & "]" & VBA.vbNewLine & _
59        "INTO [" & ResultsTable & "]" & VBA.vbNewLine & _
60        "FROM" & VBA.vbNewLine & _
61        "[#TEMP_ALL_IDS] [A] LEFT JOIN [CHECKS] [B]" & VBA.vbNewLine & _
62        "ON" & VBA.vbNewLine & _
63        "[A].[" & SequenceFieldName & "]=[B].[" & SequenceFieldName & "]" & VBA.vbNewLine & _
64        "WHERE [B].[" & SequenceFieldName & "] IS NULL" & VBA.vbNewLine & _
65        "ORDER BY [A].[" & SequenceFieldName & "]"
66    Call CurrentDb.Execute(sSQL, dbFailOnError)
67    
68    'Drop temp table
69    On Error Resume Next
70        sSQL = "DROP TABLE [#TEMP_ALL_IDS]"
71        Call CurrentDb.Execute(sSQL, dbFailOnError)
72    On Error GoTo 0
73    
74EXIT_SUB:
75    
76    Debug.Print "Execute GetMissingSeqentialIDs: " & VBA.Timer - vStartTime
77    Exit Sub
78    
79ERROR_PROC:
80    Call VBA.MsgBox(Err.Description, vbCritical, "Error GetMissingSeqentialIDs")
81    Resume EXIT_SUB
82    Resume
83    
84End Sub