Function Code_Fragment_5_4()

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

