Code required to prevent duplicate records

[cantona]

I have a database with two tables Current and Archive. At the end of each month a query is run which copies information from the current table and appends it to the archive table. Im having problems in that some of the people who use the program are running the routine twice in a month, creating duplicates in the archive table. Is there an easy way that i can check the archive table when the routine is run. If there is already a record with a particular person_id, same month and same year field in the archive, i would like a message box to pop up saying that a record already exists in the archive for this person. I would like the message box to name the person, then give the option to cancel the routine or go ahead with it. Is this possible? im relativly new to access and not really sure how to go about it.

 

[Poteen]

Cantona:
You need to use a Primary Key field in the Current database and a Foreign Key field in the Archive (comparisons of unique ID's).

Your Current database should have an primary key index column with a unique number for each record (properties set to primary, auotonumber, no duplicates). Call it CURRENT_PRIMARY

The Archive database will have a field called ARCHIVE_FOREIGN (properties set to no duplicates).

Normally when transferring records from Current to Archive database the corresponding CURRENT_PRIMARY numbers will be placed in the ARCHIVE_FOREIGN field. These are the unique identifiers for each record. If the property for the CURRENT_FOREIGN field is set to 'Yes(No Duplicates') the system will flag duplicate transfers.

Alternatively, if you wish to integrate it automatically with the transfer routine; EITHER (1)ERR trap to skip the transfer of the causative record or (2)Programmatically compare CURRENT_PRIMARY of record-being-transferred to ARCHIVE_FOREIGN numbers already existing in Archive database.
Cheers,
John

 

[cantona]

Hi! thanks for your suggestions. I would like to build this automatically within the transfer routine. I do have a primary key in the current table (person_id), and this is also in the archive table. The primary key of the archive table is archive_id. One of my problems is that the person_id can appear many times in the archive table as the information is archived off every month. I need to write a routine which when the archive button is clicked,checks through the archive table and flags up where the person_id already exists with the same month and year. It is ok if the person_id exists for a different month and year. I would then like to give the user the opportunity at this point to cancel the procedure or continue. How would i do this programmatically?

 

[Poteen]

DLOOKUP