Preventing duplicate entries by multiple field check.

[Bryan Easter]

I have a 2010 Access database that is mature. I am wanting to put a check in to stop duplicates at a certain point based on two criteria. The master table is tblConstraints in that table are two data points,MDR and Part Number, that need to be checked before a new record is created using a form.

My intent is to use the beforeupdate function on the form to check both fields MDR and Part Number. If it is a duplicate it displays a message and then cancels it. If it is not a duplicate it saves the record and moves on.

I was able to hack out this, which does prevent duplication of the MDR, but the same MDR couple have multiple part numbers so I need that extra check. Logic I am going for is if MDR and Part Number match it is a duplicate.

Here's where I am.

Private Sub MIR_BeforeUpdate(Cancel As Integer)
If (Not IsNull(DLookup("[MDR]", _
"tblConstraints", "[MDR] ='" _
& Me!MDR & "'"))) Then


MsgBox "MDR has already been entered in the database."
Cancel = True
Me!MDR.Undo
End If

End Sub

Appreciate your help and time.

Thanks.

[orange]

In your table structure (design), you could go to Indexes, and create a unique compound index that is made up of the 2 fields involved.
That's how you get the database software to prevent duplicates.

If you currently have duplicates, Access will complain and tell you it can't create that unique index because your data violates your proposed index.

Get rid of the current duplicates, then add the unique compound index to your table.

Good luck

[Bryan Easter]

I'll have to research those. I've not played with them at all.

Thanks for response.