Prevent duplicates, but not when editing existing record

[clh42]

I know the question of preventing duplicates has been asked many times before and I've found tons of messages here and elsewhere all giving the same answers, but I've not been able to find anyone else asking this specific variant of the question and I've found no answers.

I had figured out how to use the DLookup or DCount functions to check for duplicates in the BeforeUpdate event of a form and display a custom error message to the user, but I have an issue with it.

It works fine when adding a new record, but it has a pitfall when editing an existing record. For example, my table definition is this:
StatementCode1, StatementCode2, Name, Address

I have 2 existing records like this:
11111, 22222, Pete, 1234 Something St.
33333, 44444, Jack, 9876 Someother St.

I need to prevent duplicates with a combination of the StatementCode1 and StatementCode2 fields.

The user edits Pete's record to have the same combination of SatementCode1 and StatementCode2 as Jack's record. I do still want to trigger the duplicate warning, which works just fine.

The problem comes in if the user edits OTHER fields in an existing record but NOT the field that I want to prevent duplicates of. So say the user edits Pete's record to change his address. The issue is that DLookup and Dcount using the StatementCode1 and StatementCode2 fields as criteria will just find the same record currently being edited and trigger the duplicate warning, which I do NOT want in this case.

I need a way to append a way to say "and the found record is not the record currently being edited" to the search criteria for DLookup or DCount.

I did think of one way. If the table has a unique field for every record that's never modified (like an auto-number field, or a time stamp that's added when the record is first created and never changes), then you can include that in the search critera to compare the value of that field in the found record to the value of that field for the record currently being edited.

But this table doesn't have such a field and I hate to add a whole extra field just for this purpose.

I was wondering if there was a way to maybe check against the record position in the table, but I don't know how to retrieve that either from the form's data source or within the DLookup or DCount criteria. And is the record position even consistent? Can it change based on sort order and filtering?

Thank you.

 

[vbaInet]

Pretty long thread. Short concise posts get answered quicker.

I didn't read it all but from the first two paragraphs I think I understand what you're asking. If you want the check to exlcude the current record you're on you need to include the fields that make the record unique by including this check in the third argument of the DCount or DLookup functions. Do you have a Primary Key in the table?

 

[Pat Hartman]

Why not just create a unique index on the two fields and trap the error?

Alternatively, if the DCount() returns a value of 1, run it again including the ID of the current record (which is what I think vbaInet was trying to tell you). If it is still 1, ignore the error since you are editing the existing record.

 

[clh42]

Thank you both for your responses, but I have some follow up. To summarize, my ultimate goal is to provide a custom error message for a duplicate entry, but not only for entry of new records, but also when editing existing records.

Pretty long thread. Short concise posts get answered quicker.

I didn't read it all but from the first two paragraphs I think I understand what you're asking. If you want the check to exlcude the current record you're on you need to include the fields that make the record unique by including this check in the third argument of the DCount or DLookup functions. Do you have a Primary Key in the table?

I apologize for being a long post, but I was afraid someone else would comment that I didn't provide enough information, or someone would provide an answer that didn't really address my question if I didn't provide enough detail for the full situation, as I've see both of these common in many online forums in general (maybe not this one, I'm new to this one). (And I apologize again, this paragraph doesn't help with any with being short.)

As I described in the remainder of my original post, I know I need a 3rd argument, I don't know what that should be. I do have Primary Key, but it's already the combination of the 2 fields that I'm trying to prevent duplicates of. Which leads to my follow-up to the next reply.

Why not just create a unique index on the two fields and trap the error?

Alternatively, if the DCount() returns a value of 1, run it again including the ID of the current record (which is what I think vbaInet was trying to tell you). If it is still 1, ignore the error since you are editing the existing record.

I'll address those in reverse. I don't have another separate field that uniquely identifies any particular record in any way separate from the existing Primary Key, which are already the same two fields I'm trying to generate the custom error message for.

Yes, I could just add another auto-number field but I hate to do that just for this purpose when it's not needed for any other purpose.

So I was wondering if there was some sort of inherent "record position", or maybe index of a record within the table that I could reference and compare. If not, that's fine.

The error trap may work since the two fields are already the Primary Key and Access already handles the duplicates, I just want a custom error message. So what Event would I put such an error trap in? (I do know how to code the error trap, I'm just not sure what Event to put it in to intercept Access' built-in error, vs. the DLookup/DCount check being in BeforeUpdate which I think happens before Access does it's built-in check.)

Thank you again.

 

[vbaInet]

Let's see the code you're using.

 

[mdlueck]

To summarize, my ultimate goal is to provide a custom error message for a duplicate entry, but not only for entry of new records, but also when editing existing records.

I track this sort of scenario in my database applications.

On RecordAdd forms, I first do a check prior to the INSERT to see if this already is a record with the value of the field(s) which must be unique. If the coast is clear, then I go ahead and fire the INSERT Stored Procedure.

For RecordEdit forms, I do the same check with one minor twist, I specify a second WHERE condition that the record ID must not be the ID of the record being edited. So the logic involving processing the results of the "dupe check" remain the same in both cases.

I have one Stored Procedure handle both types of searches. The Stored Procedure selects the correct query based on if it received the optional arg or not.

This design has proven reliable for changes being made to the value which must be unique. As... "change" = "Edit" so the only thing excluded in that query case is the record itself. The proposed new value is checked against all of the other records in the table. No matter if a change was made to the special field or not, what is of interest is if there is a uniqueness collision with another existing record in the table... and the query does exactly that.

 

[Pat Hartman]

Use the form's On Error event to trap the duplicates. I'm not sure what the error number is so start by displaying the error number. Once you know what the number is, you can add code to trap for that error and display your own message and let Access display its own message for any other errors.

 

[clh42]

Back to my original thought from my very first post, I take it at this point (since no one has given any info on such a thing) that there is NOT some sort of inherent record position within a table that could be used to check against the same way I might check against an explicit 3rd autonumber field?

Let's see the code you're using.

The code I've been working with is the basic DCount check, or the same idea using Not IsNull(DLookup(...)). As I said, I know if I had a 3rd unique field for each record I know I could add that as a 3rd condition, but I don't. Yes, I could add an extra autonumber field, but I'd like to avoid that if possible since I have no other reason to have such a field taking up storage space in the database. If I HAVE to, I will, but I'm trying to figure out if there's another way to do it.

This code is in the form's BeforeUpdate event.

If DCount("[StatementCode1]", "Download", "[StatementCode1] = """ & Me.[StatementCode1] & """ And [StatementCode2] = """ & Me.[StatementCode2] & """") > 0 Then

MsgBox "Another record with this Statement Code already exists in the database. Duplicates are not allowed.", vbExclamation, "DUPLICATE STATEMENT CODE"
Cancel = True
Exit Sub
​

End If

This works fine for adding new records, but when editing an existing record it ALWAYS triggers the duplicate warning even if only the name or address are modified in the existing record.

...I specify a second WHERE condition that the record ID must not be the ID of the record being edited.

As mentioned above, my issue is precisely that I do not have a separate unique "ID" field in this table beyond the two fields I'm already trying to prevent duplicates in.

Use the form's On Error event to trap the duplicates. I'm not sure what the error number is so start by displaying the error number. Once you know what the number is, you can add code to trap for that error and display your own message and let Access display its own message for any other errors.

This might be what I need. I'll give it a try. Thanks!!


SIDE NOTE OF MY OWN THOUGHT:
This also popped into my head as another check I could do. When editing a record, I only need to check for duplicates if either of the fields that I want to prevent duplicates have actually been edited. So I could put an extra check around the above code checking to see if the two fields are modified. But I'm debating on how to do it.

I thought about using the text box's .Dirty property, but I thought, what if the user modifies one of the fields but then realizes they shouldn't have and manually changes it back vs. doing and "undo". Will it still be .Dirty even though it now contains the same value as it did originally? I could implement some code to test this but I think I might go with the next idea just to be safe.

If I'm not sure I can trust the .Dirty property, I can check the current value against the .OldValue property. Though then you have to deal with possible Null values and check for those separately.

The down side of both of those is that they only work with bound forms. For now though, that will work for me. But I'm still trying to think if there'd be a way to do it with unbound forms too just for thinking for the future.

 

[Pat Hartman]

I know if I had a 3rd unique field for each record I know I could add that as a 3rd condition, but I don't. Yes, I could add an extra autonumber field, but I'd like to avoid that if possible since I have no other reason to have such a field taking up storage space in the database

We were assuming that the table had an autonumber PK. If the table has no PK, you should add one. It can be an autonumber or it can be a compound natural key including the two columns in question. Making either a two-column PK or two-column unique index if you have another PK solves your problem. You can get rid of all your code and simply trap the duplicates error in the Form's Error event. You have spent 7 days trying to solve a problem you wouldn't have if you employeed RI correctly.

 

[clh42]

We were assuming that the table had an autonumber PK.

I stated in my original post that it did not have such a field. As I said, from an actual data standpoint, I don't need one for any other purpose so I was trying to avoid adding one and taking up unnecessary storage space just for the purpose of the duplicate message handling for the form, if there was another way to do it.

You can get rid of all your code and simply trap the duplicates error in the Form's Error event.

I think this is what I will do in this case and I appreciate and thank you for the recommendation. I've never use the Error event before and just hadn't thought of it.

 

[Pat Hartman]

Using the Error event requires that you make the compound, unique PK or index.

 

[clh42]

Using the Error event requires that you make the compound, unique PK or index.

Right, but as I've also already said, at least in this particular case, the fields I want to prevent duplicates of are already a compound primary key. But it still keeps me from having to add yet another separate autonumber field. I just hadn't thought of using the OnError event to trap the existing error that Access generates.

And I was trying to think for the future, what if I had a situation where I didn't have a PK or index on the desired fields, would there be another way to do it, again without adding the extra autonumber field when it isn't needed for anything else, but I guess there wouldn't be.

 

[mdlueck]

... I specify a second WHERE condition that the record ID must not be the ID of the record being edited.

As mentioned above, my issue is precisely that I do not have a separate unique "ID" field in this table beyond the two fields I'm already trying to prevent duplicates in.

All right, so then simply perform a bit more complicated of a dupe check that tip-toes around the current record based on the multi-column key. Otherwise I believe my proposal would work.

What gets complicated is that you need to perform the update based on the old values of the multi-column key and update the multi-column key to the new values. You would only do the dupe-check on the new values if they were different than the old values. If they are the same, then do not bother with the dupe-check as no changes were made to the key fields.

What it would not work for is in the event that two people are making changes at precisely the same time and end up creating a duplicate key and/or if you have an unique index across the multiple columns, then that would cause an error to be raised and then you would need code on the Error events if you wanted custom handling of the error. In my mind, let default behavior protect, the DB is just doing its job and protecting data integrity.