Function Code_Fragment_5_5()

Dim dbs As Database
Dim rst1, rst2 As Recordset


Set dbs = CurrentDb


'Here is the query for the assertion. Basically, we are looking for
'the query to be empty
Set rst1 = dbs.OpenRecordset("SELECT I1.SSN " _
                          & "FROM INCUMBENTS AS I1 " _
                          & "WHERE 1 < (SELECT COUNT(SSN) " _
                          & "           FROM INCUMBENTS AS I2 " _
                          & "           WHERE I1.SSN = I2.SSN AND I1.PCN = I2.PCN " _
                          & "           AND I1.START_DATE <= I2.END_DATE " _
                          & "           AND I2.START_DATE <= I1.END_DATE); ")
Set rst2 = dbs.OpenRecordset("SELECT * " _
                           & "FROM INCUMBENTS AS I " _
                           & "WHERE I.SSN IS NULL " _
                           & "OR I.PCN IS NULL ;")
                            

' If the result of the query is empty then the transaction is valid
' In Access, you can check if the result of a query is empty by checking
' the EOF and BOF properties of the output recordset

If (rst1.EOF And rst1.BOF) And (rst2.EOF And rst2.BOF) Then
    MsgBox ("Insertion completed")
Else
    MsgBox ("You entered an invalid input")
End If

End Function

