Results 1 to 7 of 7
  1. #1
    Alliana Gray is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    22

    preventing duplicate records

    I have two different fields in a table. Month/Year and Location.

    When a user is entering data in a form, I want a message to be displayed if the location AND month/year are the same to tell the user that they are entering a duplicate record.

    I can't put unique keys on either of these fields because there is going to have to be duplicates in each field.



    Help would be greatly appreciated!

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You can setup an index that includes both fields and set the index (i.e. the combination of the 2 fields) as unique.

  3. #3
    Alliana Gray is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    22
    How do you set an index for both the fields?

  4. #4
    Alliana Gray is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    22
    I figured out how to do the index for both fields... but I'm curious if there is a way I can modify the pop up that shows when the data is duplicated.

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I don't know of a way to change the error message. The other alternative is to use Visual Basic for Application (VBA) code to check the values prior to them being entered into the table. I believe that the code would go in the before update event of the form that you are using for data entry. The code might go something like this (air code, not tested)

    Code:
    If Dcount("*", "YourTableNameHere","field1=" & me.ControlName1 & " AND Field2=" & me.controlName2)>0 THEN
     msgbox "The values you entered already exist"
    'you will have to have some code here to clear the controls or to move the user to the record that already exists
    End if

  6. #6
    Alliana Gray is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    22
    Thanks to everyone who responded to this thread!

  7. #7
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome

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

Similar Threads

  1. Duplicate records in Form view
    By Reh in forum Database Design
    Replies: 7
    Last Post: 08-10-2011, 07:21 AM
  2. How to not show duplicate records in query?
    By JimmD43 in forum Queries
    Replies: 3
    Last Post: 05-29-2011, 02:54 PM
  3. Duplicate Records
    By softspoken in forum Queries
    Replies: 3
    Last Post: 06-21-2010, 03:33 PM
  4. Assigning values to duplicate records
    By matteu1 in forum Queries
    Replies: 3
    Last Post: 02-17-2010, 10:35 PM
  5. Delete duplicate records
    By Zukster in forum Queries
    Replies: 1
    Last Post: 08-26-2009, 03:14 AM

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