Results 1 to 6 of 6
  1. #1
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43

    Prevent duplicate entries in linked table

    Hello-

    I have a form (Add New Headcount) that inputs to a linked table. There is a field called ID that I need to prevent duplicates in. When the user opens the form and types in an ID, I want it to prevent them from submitting the new data if the ID already exists in the linked table.

    Normally you could go into the design view of the table and change the Index to prevent duplicates but because the table is linked this is no longer an option.

    Any help is appreciated! I've been struggling with this for quite some time now! Thanks.

  2. #2
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,999
    Many of us dislike the system warnings for this kind of thing and 'roll our own,' using DCount():

    Replacing Field1 with the actual name of your Field/Control, and Domain with the name of the Table or Query that holds the Field:

    If Field1 is Text

    Code:
    Private Sub Field1_BeforeUpdate(Cancel As Integer)
     If DCount("Field1", "Domain", "[Field1] = '" & Me.Field1 & "'") > 0 Then
      Resp = MsgBox("This Value Already Exists! Please Enter New Value")
      Cancel = True
     End If
    End Sub


    If Field1 is Numeric

    Code:
    Private Sub Field1_BeforeUpdate(Cancel As Integer)
     If DCount("Field1", "Domain", "Field1 = " & Me.Field1) > 0 Then
      Resp = MsgBox("This Value Already Exists! Please Enter New Value")
      Cancel = True
     End If
    End Sub

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43
    Thanks for this! I've tried doing DCount and for some reason it still doesn't work. The field name in the table "Updated Headcount" that I do not want duplicates in is called WWID. The text box in the form that populates that field is called "txtWWID". I clicked on the txtWWID in the form and put the code in the BeforeUpdate event. When I typed in a WWID that already existed nothing happened. Any idea what I'm doing wrong??

    Private Sub txtWWID_BeforeUpdate(Cancel As Integer)
    If DCount("WWID", "Updated Headcount", "[WWID] = '" & Me.WWID & "'") > 0 Then
    Resp = MsgBox("This Value Already Exists! Please Enter New Value")
    Cancel = True
    End If
    End Sub

  4. #4
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43
    Figured out the problem. Macros were disabled and there was no popup notifying it. In the trust center settings I enabled macros and now this code works perfectly. Thanks!!

  5. #5
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,999
    Glad you got it working!

    Good luck with your project!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43
    I'm actually looking back into this because we have another criteria to add.

    I have the code:

    Private Sub txtWWID_BeforeUpdate(Cancel As Integer)
    If DCount("WWID", "Updated Headcount", "[WWID] = '" & Me.txtWWID & "'") > 0 Then
    MsgBox ("This Value Already Exists! Please Enter New Value")
    Cancel = True
    End If
    End Sub


    What I need is if the value exists in the WWID column AND there is a 1 in the Number column then the message box pops up. Any ideas??

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 04-05-2013, 01:28 PM
  2. Replies: 2
    Last Post: 06-11-2012, 10:34 PM
  3. Replies: 1
    Last Post: 12-09-2011, 07:34 AM
  4. Problem with code to prevent duplicate entries
    By ResearchRN in forum Programming
    Replies: 5
    Last Post: 11-08-2011, 12:12 PM
  5. Replies: 2
    Last Post: 04-20-2011, 06:59 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums