Function Code_Fragment_7_10()

Dim dbs As Database, rst As Recordset
Dim Query 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 = "SELECT *" _
      & "FROM INCUMBENTS AS I2 " _
      & "WHERE I2.SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
      & "  AND I2.END_DATE = #12/31/9999#;"
      
Set rst = dbs.OpenRecordset(Query)

' This is the way to ask for NOT EXIST (if the result is empty then NOT EXIST is true)
If rst.EOF And rst.BOF Then
    dbs.Execute "INSERT INTO INCUMBENTS " _
              & "VALUES (" & Chr(34) & "111223333" & Chr(34) & ", " & Chr(34) & "341288" & Chr(34) & ", " _
              & "DATE(),#12/31/9999#);"
              
    dbs.Execute "UPDATE INCUMBENTS " _
              & "SET END_DATE = DATE() " _
              & "WHERE SSN = " & Chr(34) & "111223333" & Chr(34) & " " _
              & "  AND PCN <> " & Chr(34) & "341288" & Chr(34) & " " _
              & "  AND END_DATE = #12/31/9999#;"
    MsgBox ("Insertion completed")
Else
    MsgBox ("You entered an invalid input")
End If

End Function
