Syntax for INSERT INTO [table1] VALUES, WHERE NOT IN [Table2]

[David Hargreave]

Hi I am tring to insert into a table a record from afile dialog box , but if the record exists in the table i am inserting into I want it to not insert and drop the record.

Sub Letters()

DoCmd.SetWarnings False
Dim FileDate As String
Dim LetterCode As String
Dim MyArray As Variant
Dim fd As FileDialog
Dim updatesql As String
Dim FileName As String
Dim XLApp As New Excel.Application
Dim AmRecs As String
Dim vrtSelectedItem As Variant
Dim Amount As String
Dim status As String
Dim id As String
Dim NFileName As String
Dim Counter As Integer
Dim pp As Object

MyArray = Array(Array(0, 1), Array(50, 1), Array(60, 1))
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Set XLApp = CreateObject("Excel.application")

status = "OutStanding"
Counter = "0"

'Use a With...End With block to reference the FileDialog object.
With fd

'Use the Show method to display the File Picker dialog box

'The user pressed the action button.
If .Show = -1 Then

'Step through the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem contains the path of each selected item.
FileName = vrtSelectedItem
                           
updatesql = "INSERT INTO [Requests1]([FileName],[Amount],[Status])" & _
"values('" & FileName & "', '" & Amount & "', '" & status & "')"
            DoCmd.RunSQL (updatesql)
    
    
            Next vrtSelectedItem

        End If
    
    End With

'Set the object variable to Nothing.
Set fd = Nothing

[ADezii]

The general idea would be:

'Code intentionally omitted...
For Each vrtSelectedItem In .SelectedItems
  FileName = vrtSelectedItem
    'See if Filename exists in the Requests1 Table, if it does DELETE Record,
    'and if it doesn't INSERT a New Record into Requests1 based on FileName only
    If DCount("*", "Requests1", "[FileName] = '" & FileName & "'") > 0 Then
      CurrentDb.Execute "DELETE * FROM Employees WHERE [Firstname] = '" & Me![Text98] & "'", _
                   dbFailOnError
    Else
      updatesql = "INSERT INTO [Requests1]([FileName],[Amount],[Status])" & _
                  "values('" & FileName & "', '" & Amount & "', '" & Status & "')"
        DoCmd.RunSQL (updatesql)
    End If
Next vrtSelectedItem
'Code intentionally omitted...