Hello,

I have the following query that inserts some values in a table. Now it is possible that some of these values already exist in this table.

How can i find out what can be inserted and what not?

This is the query:

strSQL2 = " INSERT INTO Personeels_evaluaties ( Personeelsnummer, Itemnummer ) " & _
" SELECT Personeelsfuncties.Personeelsnummer, " & Me.lstitems & " AS Itemnummer " & _
" FROM Personeelsfuncties " & _
" WHERE ((Personeelsfuncties.FunctieID) = " & Me.lstfuncties & ") And ((Personeelsfuncties.MachineId) = " & Me.lstmachine & "); "

Best regards,
Sven.

Not sure of the error you are getting or what-have-you, but let's suppose that it is erroring on "Personeelsnummer" because it already exists (ie, no duplicates on that field).

You could do a test of sorts prior to your adding ...


If IsNull(DLookup( ...... )) Then
'do an insert
Else
'do an update
End If

That is, if your question is to do an insert verses an update because the record already exists. The DLookup checks for a record where the "Personeelsnummer" = Me.txtControlNameWithPersoneelsnummer. If the record exists, then you would update the other fields as it relates to that personnel number.

If all fields are set to no duplicates then you might have to expand for individual testing prior to inserting and/or updating.

-dK

Hello,

indeed i have to give more information ...

I get an error because the personeelsnummer and the the itemnummer both exist. the primary key is the combination of the two fields. Now for my insert query ... the results are many records so it is not possible to check for every record with dlookup if it exist or not.

So I am looking for a way to exclude all the records that already exist in the table personeels_evaluaties.

Is this possible ? And if so How?

Thanks in advance,
Sven.

Ooooo. I am sorry, but your request is beyond my skillsets - perhaps someone with deeper experience can provide a good solution.

My only thoughts would be to populate a table with what you have and then do a table comparison (unmatched) and finally append the unmatched records to the main table.

Or, to use DAO or ADO to start looping through each record, comparing, and appending.

-dK