Insert into table doesn't check for duplicate records!! (1 Viewer)

Pelerin13

Registered User.
Local time
Today, 13:06
Joined
Mar 23, 2011
Messages
56
Hey All,
I have this code to copy records from the ImportSerial table into ImportSerial_Shipped table where the SO_Finished diff than 11/11/1111
Code:
[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         dbs.Execute "INSERT INTO ImportSerial_Shipped SELECT * FROM ImportSerial WHERE ([ImportSerial].[SO_Finished] Not Like " & "'*11/11/1111*'" & ");", False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]

So far the coping work fine, but I have a field called S/N that’s cannot be duplicate. And I want to get an Error msg when there is a duplicate

Code:
[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]         MsgBox "Error Number:  " & err.Number & "  " & err.Description, vbOKOnly, "Export Failed"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]

Please assist
Thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:06
Joined
Jan 23, 2006
Messages
15,364
Make sure your design of table ImportSerial_Shipped includes a Primary Key.
This will not allow duplicates.

Set up an error handling routine and trap for duplicate record , I think the Err number is 3022.
 

Pelerin13

Registered User.
Local time
Today, 13:06
Joined
Mar 23, 2011
Messages
56
thanks jdraw for the reply,
the [S/N] is the Primary Key in the Serial_Shipped table, but the problem is that the code run with no error even if there is a duplicate. but it doesn't copy the new record instead it keep the old one there.
all i want it to do is the rais an error msg saying the there a a record in Serial_Shipped table has the same S/N.
i set up the error handling before but it doesn't fall into it
Code:
If err.Number <> 0 Then
    DoCmd.Close acForm, "PLZWAIT", acSaveNo
    'MsgBox err.Description
    MsgBox "Error Number:  " & err.Number & "  " & err.Description, vbOKOnly, "Export Failed"
End If
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:06
Joined
Jan 23, 2006
Messages
15,364
In your dbs.execute sqlstring,dbFailOnError

I believe this will
-roll back any record with an error
-pass errors to an error handler

As an alternative, I think you could use recordsets and loops and do a DCount to see if the record being exported to your second table already exists. Then take your appropriate action. It may be slower, but you'd be able to handle the errors.

Untested, but best guess.
 

Pelerin13

Registered User.
Local time
Today, 13:06
Joined
Mar 23, 2011
Messages
56
i added "dbFailOnError" and that did the trick
i could find that part anywhere ... i used true/false nothing work

i realy appreciate it ... thanks again
 

Pelerin13

Registered User.
Local time
Today, 13:06
Joined
Mar 23, 2011
Messages
56
btw, i don't know if this is possible or not!
is there a way to include the S/N that was duplicated in the error msg?
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:06
Joined
Jan 23, 2006
Messages
15,364
Glad you got it working.
 

Pelerin13

Registered User.
Local time
Today, 13:06
Joined
Mar 23, 2011
Messages
56
btw, i don't know if this is possible or not!
is there a way to include the S/N that was duplicated in the error msg?
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:06
Joined
Jan 23, 2006
Messages
15,364
You could certainly do that when using loop with recordset. You would have access to every record and every insert.
I think the db.execute does the insert in bulk so to speak.

Did you get more than one duplicate error when you ran the code? I was going to post that I set up a test and I only got the first failing record and the procedure exited. ( I just deleted the test records and tables) I was a little surprised when you said it worked.
Did it really find all the duplicates? I can't see a way of adding the current S/n to an error message. Maybe someone else knows for sure.
 

Pelerin13

Registered User.
Local time
Today, 13:06
Joined
Mar 23, 2011
Messages
56
i hope so,
but yes the code worked and it doesn't find the duplicate but once there one the error msg pop up.
and that exactly what i want.
i tried the loop but it is bit confusing

please if someone has any idea about "is there a way to include the S/N that was duplicated in the error msg? "
would be much appreciated
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:06
Joined
Sep 12, 2006
Messages
15,614
a thought/observation

you have three approaches to executing queries, all with fors and againsts.

a) currentdb.execute, dbfailonerror

doing it this way traps an error, and stops the process. I am not 100% sure - It used to fail completely in the case of an error, and no records got updated - although I thought some one posted that it now processed the items that were not in error. Can anyone comment on this?

b) docmd.runsql (or docmd.openquery)
definitely warns you about errors, but does process successful items. However the warning messages can be used to stop the process, which may not be what you want

wrapping this with docmd.setwarnings false and docmd.setwarnings true suppresses the error message, but then you don't realise that some actions failed.

c) Use code
write code to iterate your query recordset, and process the items individually - needs more work, but gives you closer control. And now you can develop more precise error messages


so it's a matter of taste to get the result you want.
 

Pelerin13

Registered User.
Local time
Today, 13:06
Joined
Mar 23, 2011
Messages
56
Thanks Dave,
a) currentdb.execute, dbfailonerror
I confirm that if you do have an error handler procedure in your code (On Error GoTo ...) it will definitely stop the executing the rest of the code and do whatever you want it to do. but you got to have ,dbfailonerror.

the only reason I don’t like b) docmd.runsql (or docmd.openquery) is because it shows you how many it will appends even if there is no error and I don’t like that.

This is my Loop procedure but it is not working, is doesn’t even get into the loop
Code:
[/B][/FONT][/COLOR][/B]
[COLOR=black][FONT=Verdana]Private Sub Command245_Click()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] Dim SID As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim stLinkCriteria As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim rsc As DAO.Recordset[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim dbs As DAO.Database[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim rscS As DAO.Recordset[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Dim sql As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]On Error GoTo ErrorHandler1[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Set dbs = CurrentDb()[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Set rscS = dbs.OpenRecordset("ImportSerial_Shipped", dbOpenDynaset, dbAppendOnly)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'sql = "SELECT  [ImportSerial].[S/N], FROM [ImportSerial] UNION SELECT  [ImportSerial_Shipped].[S/N], FROM [ImportSerial_Shipped] GROUP BY  [ImportSerial_Shipped].[S/N] ;"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] sql = "INSERT INTO ImportSerial_Shipped SELECT * FROM ImportSerial WHERE ([ImportSerial].[SO_Finished] Not Like " & "'*11/11/1111*'" & ");"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] If rscS.BOF And rscS.EOF Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]   Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]      Do Until rscS.EOF[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            If rscS![S/N] Like "'%1CKSA51408'" Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                 DoCmd.RunSQL sql[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]            rscS.MoveNext[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       Loop[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    If Not rscS Is Nothing Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        rscS.Close[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Set rscS = Nothing[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]ErrorHandler1:[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If err.Number <> 0 Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    MsgBox "Error Number:  " & err.Number & "  " & err.Description, vbOKOnly, "Export Failed"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If err.Number = 0 Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    MsgBox "Error Number:  " & err.Number & "  " & err.Description, vbOKOnly, "Export Failed"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]
[SIZE=3][FONT=Calibri]
[/SIZE]
 

Pelerin13

Registered User.
Local time
Today, 13:06
Joined
Mar 23, 2011
Messages
56
here is what i did to get what i seed and is working perfect LOL
i used the code to check if there is duplicate currentdb.execute, dbfailonerror
then i created a query to find the duplicate SNs,
Code:
SELECT ImportSerial_Shipped.[S/N] AS [Shipped S/N], ImportSerial_Shipped.SalesON AS [Shipped SO], ImportSerial.[S/N] AS [Not Shipped S/N], ImportSerial.SalesON AS [Not Shipped SO]
FROM ImportSerial, ImportSerial_Shipped
GROUP BY ImportSerial_Shipped.[S/N], ImportSerial_Shipped.SalesON, ImportSerial.[S/N], ImportSerial.SalesON, [importSerial_Shipped].[S/N]=[ImportSerial].[S/N]
HAVING ((([importSerial_Shipped].[S/N]=[ImportSerial].[S/N])<>0));

and i call a form that uses that query everytime i get the error code number 3022

DoCmd.OpenForm "Find duplicates for ImportSerial_Union", acNormal

Thanks All again
 

jdraw

Super Moderator
Staff member
Local time
Today, 16:06
Joined
Jan 23, 2006
Messages
15,364
a thought/observation

you have three approaches to executing queries, all with fors and againsts.

a) currentdb.execute, dbfailonerror

doing it this way traps an error, and stops the process. I am not 100% sure - It used to fail completely in the case of an error, and no records got updated - although I thought some one posted that it now processed the items that were not in error. Can anyone comment on this?

b) docmd.runsql (or docmd.openquery)
definitely warns you about errors, but does process successful items. However the warning messages can be used to stop the process, which may not be what you want

wrapping this with docmd.setwarnings false and docmd.setwarnings true suppresses the error message, but then you don't realise that some actions failed.

c) Use code
write code to iterate your query recordset, and process the items individually - needs more work, but gives you closer control. And now you can develop more precise error messages


so it's a matter of taste to get the result you want.

Dave,
I set up a test to INSERT some records into a Table that had a PK and a name field. There were 2 records in the Table.

Code:
sql="Insert into TablewithDups2 (id,name) Select field1 as id,field2 as name from TablewithDups"

I used db.execute sql, dbFailOnError.

Test 1. There was a duplicate in first record of the incoming data.

Error message was raised and no records were inserted.

Test 2. There were no records in the final table, but there were duplicates in the incoming data.
Error message was raised and no records were inserted.

Test 3. No records in the final table, and no duplicates in the 8 records in the incoming data.

No error message, 8 Records were inserted.

Conclusion: dbFailOnError is a all or nothing situation. If any error occurs, a message is triggered and no records are modified. Only if there is no error will the sql be actioned.

Agree totally with your option c. You can code specific error messages and return to the Loop to process other records. More work, but more control.
 

Users who are viewing this thread

Top Bottom