MS ACCESS Relationship and Duplicate

[nooblang]

Hi,

I have ms access database with two tables connected by "ID Number" as primary using a one-to-many relationship. My question is how can i create a code or something like that , that would notify me about duplicate entry.

for ex:

table 1 with ID number, name, address
connected with relationship
table 2 with ID number, date, remarks

entry 1

ID number | name | address | date | remarks

00-1 | Nat | California | 1-14-14 | present
00-2 | Rea | California | 1-14-14 | present

entry 2

ID number | name | address | date | remarks

00-1 | Nat | California | 1-15-14 | present
00-2 | Rea | California | 1-14-14 | present


Note: if i do enter same date in id-number 00-2 it should give me a note that it was or it is a duplicate entry.

need help..correctio n about my question is accepted

[ADezii]

Assuming the [ID Number] Field in Table1/Table2 is TEXT, then

If DCount("*","Table2","ID Number = '" & <ID to Test> & "'") > 0 Then
  'ID already exists in Table1, a Duplicate
End If

[Rabbit]

There's no code needed. Use a composite primary key if you don't already have a key on that table. Otherwise, use a composite unique index.

[Tom Murphy]

You will not get duplicates if you make the key in the second table consist of the ID and the date. Then when you enter the data in the second table, it will not allow you to enter a second record with the same ID and date.

If you are just trying to find duplicates in the second table, do a Find Duplicates query.

[ADezii]

You can easily create a Unique, Composite Index on the [ID] and [Date] Fields but a Duplication Error that is generated will be cryptic and confusing to the User. In the additional to the Index, and assuming you are manipulating the Data via a Form, code in the BeforeUpdate() Event of the Form can easily check for Duplication, provide a more meaningful Error Message, and Cancel the Update of the Form. The Index would still be in place as a secondary measure. An example of an Error Message using this approaqch would be:

An entry for ID: [00-2] and Date: [1/1/2014] already exists and cannot be duplicated

Code Example:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "Table2", "[ID Number] = '" & Me![txtIDNumber] & _
          "' AND [Date] =#" & Me![txtDate] & "#") > 0 Then
  MsgBox "An entry for ID: [" & Me![txtIDNumber] & "] and Date: [" & Me![txtDate] & _
         "] already exists and cannot be duplicated", vbExclamation, "Record Duplication"
    Cancel = True
End If
End Sub