Function Code_Fragment_5_16()

Dim dbs As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim Record_Number As Long

Set dbs = CurrentDb


'Here is the query for the assertion. Basically, we are looking for
'the query to be empty
Set rst2 = dbs.OpenRecordset("SELECT * " _
                          & "FROM INCUMBENTS AS I " _
                          & "WHERE  NOT EXISTS ( " _
                          & "SELECT * " _
                          & "FROM POSITIONS AS P " _
                          & "WHERE I.PCN = P.PCN " _
                          & "AND P.START_DATE <= I.START_DATE " _
                          & "AND I.START_DATE < P.END_DATE ) ")
                          
Set rst1 = dbs.OpenRecordset("SELECT * " _
                          & "FROM INCUMBENTS AS I " _
                          & "WHERE NOT EXISTS ( " _
                          & "SELECT * " _
                          & "FROM POSITIONS AS P " _
                          & "WHERE I.PCN = P.PCN " _
                          & "AND P.START_DATE < I.END_DATE " _
                          & "AND I.END_DATE <= P.END_DATE ) ")
                          
' 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) Or (rst2.EOF And rst2.BOF) Then
    MsgBox ("Insertion completed")
Else
    MsgBox ("You entered an invalid input")
End If

End Function
