Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-12-2018, 10:10 AM   #1
ivonsurf123
Newly Registered User
 
Join Date: Dec 2017
Posts: 30
Thanks: 8
Thanked 0 Times in 0 Posts
ivonsurf123 is on a distinguished road
How to avoid duplicates in append query

Hello,

I am trying to check on duplicates if I enter the data again, but it only works if I enter Both values but if I enter just one it take it as new entry and not recognized that single value already exist in Table, Example: if I have the Vendor and Code it will tell me that value already exists in tbl_CDS_DRA_Key_Master_Codes, but if I do it again but this time I add Vendor only it will record the data, How can I prevent that and recognize whether the Vendor or Code already exists in tbl_CDS_DRA_Key_Master_Codes, data in coming from another table named: tbl_CDS_DRA_Key_Legend


If DCount("*", "tbl_CDS_DRA_Key_Master_Codes", "[Vendor]='" & Me.txtExtractType & "' AND [Code]='" & Me.txtCode & "'") > 0 Then

MsgBox "This code already exists in the Master Code File!" & vbCrLf & _
"Vendor: " & Me.txtExtractType & vbCrLf & _
"Code: " & Me.txtCode & vbCrLf, vbCritical, "Already Exists"
Me.Undo
Cancel = True

Else

strNewDRA = "INSERT INTO tbl_CDS_DRA_Key_Master_Codes " & _
"([Vendor],[Code]) VALUES ('" & Me.txtExtractType & "','" & Me.txtCode & "'); "
CurrentDb.Execute strNewDRA
MsgBox "DRA Key Moved to the Master Codes File Completed", vbInformation, "DRA Key"

End If

ivonsurf123 is offline   Reply With Quote
Old 01-12-2018, 10:52 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,274
Thanks: 47
Thanked 1,817 Times in 1,767 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: How to avoid duplicates in append query

You might research this link re composite unique index to prevent duplication of data.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 01-12-2018, 11:03 AM   #3
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,992
Thanks: 54
Thanked 1,923 Times in 1,837 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: How to avoid duplicates in append query

Use OR and not AND on yout Dcount()

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
ivonsurf123 (01-12-2018)
Old 01-12-2018, 11:04 AM   #4
ivonsurf123
Newly Registered User
 
Join Date: Dec 2017
Posts: 30
Thanks: 8
Thanked 0 Times in 0 Posts
ivonsurf123 is on a distinguished road
Re: How to avoid duplicates in append query

Thank you arnelgp! I was complicating myself
ivonsurf123 is offline   Reply With Quote
Old 01-12-2018, 04:54 PM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,092
Thanks: 68
Thanked 1,371 Times in 1,293 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: How to avoid duplicates in append query

Do take heed of jdraw's post too.

A composite index should be present to ensure that duplicates cannot be entered under any circumstance.
Galaxiom is offline   Reply With Quote
Old 01-13-2018, 07:05 AM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,942
Thanks: 13
Thanked 1,318 Times in 1,257 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: How to avoid duplicates in append query

Whenever RI (Referential Integrity) can do something for you, you should use RI instead of relying on your own code. If each column must be unique by itself, you need two separate unique indexes. When the combination of the two columns must be unique, you need ONE compound index that includes both columns.

Having the indexes prevents queries and other processes from adding invalid data. Your code only works for the form where it is running and possibly not in all cases unless you have put the code into the correct event - which is the form's BeforeUpdate event. If your code is in any other event, it will not actually protect anything.

PS - undoing all the changes to the form is punitive. Why not just undo the value for the field that is duplicated? Of course you would have to separate the DLookup() into two parts so you can identify which field has the error. The suggested code using the OR can't distinguish and so if you want to undo, you have no alternative but to undo BoTH or even the entire form.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append query - avoid duplicate records Jen_RT Queries 19 06-02-2010 06:30 AM
Please check my Insert Query (avoid inserting duplicates) MsLady Queries 3 11-28-2007 11:41 AM
[SOLVED] Simple query -- how to avoid duplicates? TheMadHatter Queries 4 12-05-2005 09:29 AM
How can I avoid duplicates in a query programmatically? TryingMyBest Modules & VBA 4 07-05-2005 09:14 AM
How to avoid duplicates in append query wind54surfer Queries 1 06-13-2005 10:21 PM




All times are GMT -8. The time now is 09:21 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World