Function Code_Fragment_7_3()

Dim dbs As Database
Dim rst_a, rst_b As Recordset
Dim Query_a, Query_b As String

Set dbs = CurrentDb()

'Since Access does not support the statement INSERT INTO VALUES .... WHERE
'You must first check the where clause and depending on the result allow or disallow the insertion

' This is the way to chek the WHERE clause
Query_a = "SELECT *" _
        & "FROM INCUMBENTS AS I2 " _
        & "WHERE I2.SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
        & "  AND I2.END_DATE = #12/31/9999#;"
Query_b = "SELECT  * " _
        & "FROM POSITIONS " _
        & "WHERE POSITIONS.PCN = " & Chr(34) & "341288" & Chr(34) & " " _
        & "  AND POSITIONS.END_DATE = #12/31/9999#;"
      
Set rst_a = dbs.OpenRecordset(Query_a)
Set rst_b = dbs.OpenRecordset(Query_b)

' This is the way to ask for NOT EXIST (if the result is empty then NOT EXIST is true)
If (rst_a.EOF And rst_a.BOF) And Not (rst_b.EOF And rst_b.BOF) Then
    dbs.Execute "INSERT INTO INCUMBENTS " _
              & "VALUES (" & Chr(34) & "111223333" & Chr(34) & ", " & Chr(34) & "341288" & Chr(34) & ", " _
              & "DATE(),#12/31/9999#);"
    MsgBox ("Insertion completed")
Else
    MsgBox ("You entered an invalid input")
End If

End Function
