preventing duplicate records

[Alliana Gray]

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!

[jzwp11]

You can setup an index that includes both fields and set the index (i.e. the combination of the 2 fields) as unique.

[Alliana Gray]

How do you set an index for both the fields?

[Alliana Gray]

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.

[jzwp11]

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)

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

[Alliana Gray]

Thanks to everyone who responded to this thread!

[jzwp11]

You're welcome