By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,192 Members | 1,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,192 IT Pros & Developers. It's quick & easy.

MS ACCESS Relationship and Duplicate

P: 1

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..correction about my question is accepted
Feb 26 '14 #1
Share this Question
Share on Google+
4 Replies

Expert 5K+
P: 8,638
Assuming the [ID Number] Field in Table1/Table2 is TEXT, then
Expand|Select|Wrap|Line Numbers
  1. If DCount("*","Table2","ID Number = '" & <ID to Test> & "'") > 0 Then
  2.   'ID already exists in Table1, a Duplicate
  3. End If
Feb 26 '14 #2

Expert Mod 10K+
P: 12,366
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.
Feb 26 '14 #3

P: 5
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.
Feb 27 '14 #4

Expert 5K+
P: 8,638
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:
Expand|Select|Wrap|Line Numbers
  1. An entry for ID: [00-2] and Date: [1/1/2014] already exists and cannot be duplicated
Code Example:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If DCount("*", "Table2", "[ID Number] = '" & Me![txtIDNumber] & _
  3.           "' AND [Date] =#" & Me![txtDate] & "#") > 0 Then
  4.   MsgBox "An entry for ID: [" & Me![txtIDNumber] & "] and Date: [" & Me![txtDate] & _
  5.          "] already exists and cannot be duplicated", vbExclamation, "Record Duplication"
  6.     Cancel = True
  7. End If
  8. End Sub
Feb 27 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.