Append new records but don't create duplicates

[Guest]

I have an an append query that copies entries from one field in one table to a field of the same name in another table. I have a macro that opens the append query every time the first table is updated. However, I don't want duplicates of existing entries to be copied to the 2nd table. How can I modify my query, or work with multiple queries to either:
- avoid creating duplicate records to begin with or
- delete duplicate records so that only one of the duplicates exists

I don't want the database user's to receive any of MS Access's built-in warnings when any query runs.


Details:
I have a 1:1 relationship between two tables, Contracts and Customers. Contracts includes the field Customer_Name. Customer has the fields Customer_Name and Customer_Type.
e.g. 1 if the the Customer_Name is 'Microsoft', then the Customer_Type may be 'Industry'.
e.g. 2 if the Customer_Name is 'US Dept. of Agriculture' then the Customer_Type is 'Government'.
I don't want duplicates of 'Microsoft' or 'US Dept. of Agriculture' appearing in the Customer table.

 

[Newbie]

If you have the primary keys set correctly you will be able to run an append
query without a problem. The query will only enter records that don't
produce duplicates.
In order to turn off the MS warning message do the following:

docmd.setwarnings false
docmd.openquery "appendqueryname"
docmd.setwarnings true

HTH

I have an an append query that copies entries from one field in one table

to a field of the same name in another table. I have a macro that opens the
append query every time the first table is updated. However, I don't want
duplicates of existing entries to be copied to the 2nd table. How can I
modify my query, or work with multiple queries to either:

- avoid creating duplicate records to begin with or
- delete duplicate records so that only one of the duplicates exists

I don't want the database user's to receive any of MS Access's built-in warnings when any query runs.


Details:
I have a 1:1 relationship between two tables, Contracts and Customers.

Contracts includes the field Customer_Name. Customer has the fields
Customer_Name and Customer_Type.

e.g. 1 if the the Customer_Name is 'Microsoft', then the Customer_Type may be 'Industry'.
e.g. 2 if the Customer_Name is 'US Dept. of Agriculture' then the Customer_Type is 'Government'.
I don't want duplicates of 'Microsoft' or 'US Dept. of Agriculture'

appearing in the Customer table.

 

[Guest]

Where do I paste this code? I've used a macro to automate the OpenQuery command, not a module.

 

[Newbie]

In your macro before the entry to open the query add another line
SetWarnings ---- change this to Yes or No (not sure which as I haven't used
it)
After the entry to open the query add another line
SetWarnings ----- change this to the opposite of whatever works in the
previous line

HTH

 

[Guest]

It worked!

Thanks

In your macro before the entry to open the query add another line
SetWarnings ---- change this to Yes or No (not sure which as I haven't used
it)
After the entry to open the query add another line
SetWarnings ----- change this to the opposite of whatever works in the
previous line

HTH