Append Query is Able to Repeat the Entry

[ddrew]

I am running an Append Query [qry_AppendToWeatherFromGP] to update some comboboxes as part of an import function.

My query is:

INSERT INTO tbl_Weather ( Weather )
SELECT tbl_DetectionImports.Weather
FROM tbl_DetectionImports;

The function is:

Private Sub btn_SaveImport_Click()
    On Error GoTo btn_SaveImport_Click_Err

    DoCmd.SetWarnings False

    DoCmd.OpenQuery "qry_AppendToWeatherFromGP", acViewNormal, acEdit

    DoCmd.OpenQuery "qry_DeleteGPTrainingImports"

    DoCmd.SetWarnings True

    MsgBox "Record has been imported"
    Me.Requery
btn_SaveImport_Click_Exit:
    Exit Sub

btn_SaveImport_Click_Err:
    MsgBox Error$
    Resume btn_SaveImport_Click_Exit

End Sub

My problem is that if the item is already in the list it will put it in regardless. How can I prevent this from happening

 

[namliam]

simply make an outer join query
Something like

SELECT tbl_DetectionImports.Weather
FROM tbl_DetectionImports
Left join tbl_Weather on tbl_Weather.Weather = tbl_DetectionImports.Weather
where tbl_Weather.Weather  is null

 

[pr2-eugin]

How about rewriting the CODE to a RunTime Query?

Private Sub btn_SaveImport_Click()
    On Error GoTo btn_SaveImport_Click_Err
    CurrentDB.Execute "INSERT INTO tbl_Weather ( Weather ) " & _
                      "SELECT tbl_DetectionImports.Weather " & _
                      "FROM tbl_DetectionImports " & _
                      "WHERE tbl_DetectionImports.Weather NOT IN " & _
                      "(SELECT tbl_Weather.Weather FROM tbl_Weather);"

    DoCmd.OpenQuery "qry_DeleteGPTrainingImports"

    MsgBox "Record has been imported"
    Me.Requery
btn_SaveImport_Click_Exit:
    Exit Sub

btn_SaveImport_Click_Err:
    MsgBox Error$
    Resume btn_SaveImport_Click_Exit

End Sub

 

[ddrew]

simply make an outer join query
Something like

SELECT tbl_DetectionImports.Weather
FROM tbl_DetectionImports
Left join tbl_Weather on tbl_Weather.Weather = tbl_DetectionImports.Weather
where tbl_Weather.Weather  is null

Do I still need to make it an Append Query?

 

[pr2-eugin]

Yes, the INSERT query's SELECT part should be replaced with the SELECT namliam provided. Good luck.