Catch Duplicate Records

[RBro-42]

New Member to Forum,


Hello, I am writing a new MS Access Database using Access 2016. I have a form named “Discrepancy”. This database is to be used on large industrial projects where there will likely be many discrepancies. There are Three Fields that I want the VB Code Duplicate Record to catch.

• Tag_Line-ID
• DiscrepancyKeyword

Should these two fields match then “Message Box- There is an existing record that matches this entry. Do you wish to continue? Yes – No.
If yes then allow data entry to continue to next field, “Description”

• Tag_Line-ID
• DiscrepancyKeyword

• Description

Should all three fields match then discontinue record and delete this record.
The issue with Discrepancies on large projects is someone will discover a discrepancy then two-three week later someone else will report the exact same discrepancy. Sometimes 3-4 entries are recorded for same item. However sometimes an “almost” same entry may be a legitimate discrepancy. Such as a Line Tag_Line-ID = ABC-0000, DiscrepancyKeyword = Flange, but pipelines have many flanges and the first and second entries may at different locations.
Then the Description field would be the final match catch.
I am thinking that TWO Field AFTER UPDATE code entries should be used, First look after DiscrepancyKeyword entry, then if continue equals yes the SECOND code look after Description UPDATE.
This code is a little too complex for me as I am a novice with VB code.
Any help would be appreciated, Thanks

[Micron]

Shouldn't be too hard, but as usual I have questions. Why would I cancel when the 1st two are identical? Would I not want to see if my last entry is unique? Your requirement should be based on human interpretation, not computer logic? If I can blindly enter the 3rd field and it passes the test, it is no guarantee that there won't be duplicates. My entry of "receiver" will pass because someone else entered "reciever". Shouldn't you be showing a list of current discrepancy records for a project? That is, project details on top, discrepancies below in a sub form, and if a new one needs to be entered it could be easily determined?

[RBro-42]

Thanks for the response, Most times the Discrepancy list get pretty large 500 + records. Making a "Description" Sub form may be a work around though. When a contractor learns he has 500 items in a discrepancy list in a project progress meeting and THEN discovers 5 of the discrepancies are exactly same, whoa! he isn't nice about it. If you aren't sure about the VB Code to do this event then thanks anyway. I may just use your sub-form suggestion.

[Micron]

I wouldn't consider a subform a work around. It is the typical design approach when there is a one (project) to many (notes) relationship. I suppose you could try using code to control duplicates going forward if there is a need. Perhaps if the subform has too many records to scroll through then you still should do something. Maybe if I enter tag line, the subform filters to those values. Then if I enter keyword, form filters further. Perhaps that list would then be small enough to be reviewed? This approach might require a staging table so that issues with creating duplicate/incomplete records are not raised. In such a table, a record is copied to it and user can do whatever without causing any grief. Validation can then be done on the completed record, and if all OK, it is appended to the main table(s) and the temp data is deleted.

As for the original idea, I envision it like this:

- in order to cancel a record, a BeforeUpdate event is required
- the event should only be on the 2nd field
- a test needs to be made to see if both fields are filled in. If not, cancel the event.
- if both values are entered, DLookup to find if table contains both entries
- if found, prompt
- if No, cancel. If Yes, move to next field
- user enters 3rd value and saves record

Does that cover the requirement?
EDIT -forgot to say that if you really have special characters in any object names, it's considered poor practice to do that, or to use reserved words. I only see a hint of the former: Tag_Line-ID

[orange]

If you have a Primary Key, or Unique Index and try to save a duplicate, then Access will report an Error 3022 indicting an attempt to save a duplicate. You could intercept/bypass that message and return to your regular processing.

[RBro-42]

The Sub-Form will work. There is hardly ever more than 3 duplicates for a discrepancy. I can not use a unique sometimes there is a reason to enter a second same discrepancy only with a different description to clarify the difference.
I already have a sub-form in another form (for a different reason) and it works great.
Again Thanks for your suggestion.

[Micron]

I can not use a unique sometimes there is a reason to enter a second same discrepancy only with a different description to clarify the difference.

You certainly can have a 3 field unique index, which would probably work for you. As long as the 3rd field isn't the same as any other record where the first 2 are the same you should be good.

[RBro-42]

Actually I like your Sub-Form suggestion best. I had already tried the 3-Field unique index option prior to looking for online assistance. The 1st, 2nd and also the 3rd fields all must contain same information. That is 1st field = same Tag_Line ID, 2nd Field = same DiscrepancyKeyword, 3rd Field same Discription.
I may take another look at the Index feature though, and still use the sub-form also. I could use the index option on the sub-form as all three fields are on it.