Access Table - Do not allow user to input duplicate record based on multiple fields

try creating an index using the four fields

open table in design view
click on indexes

myIndex   Column1
              Column2
              Column3
              Column4  

Good suggestion from capricorn1.
Make it unique as well to prevent the duplicates.

http://bytes.com/topic/access/answers/205054-how-make-composite-unique-index

Also set "Ignore Nulls" to Yes.

It's also not very intuitive how to create an Index with multiple Columns, so to expand on what Cap wrote:

In the first blank row of your Indexes dialog, in the "Index Name" column, enter a name for your new Index (You can call it 'MyIndex', as suggested by cap). In the second column (the "Field Name"), select your first field. Set your index properties as well (i.e Primary, Unique, etc).

Now drop to the next blank line but do NOT enter anything in the Index Name column. Move to the Field Name column and select your second field, then do the same for other fields.

Just to Clarify what Ignore Nulls means - From Help:

"Ignore Nulls Property
You can use the IgnoreNulls property to specify that records with Null values in the indexed fields not be included in the index."

mx

Hi,

I created the index using various combinations of yes/no in the index properties but I can't get it to work.  Do I need to apply the index somehow?  I haven't ever used an index before.

No, your changes should take effect immediately. You should only set the Index properties one time, on the line where you specify the Index name. See the image below
CompositeIndex.png

Is this what you did ?
Capture1.gif

And once you get the Unique Index working, you can add this code to the Form Error event to trap when a user tries to enter a duplicate set of those fields:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Response = acDataErrContinue
    Select Case DataErr
        Case 3022
            MsgBox "You entered a duplicate record"
        Case Else
            MsgBox "An unexpected error occurred " & daterr & "  " & AccessError(DataErr)
    End Select
End Sub

mx

Hi Experts,

I tried to remodel the above images but I was still permitted to create duplicate records as shown in my attached images....(not attched was attempt 3 with 'ignore nulls' set to yes)
image1.png
image2.png
image-3.png

Yeah, as I recall, this is a problem when you are allowing, via your UI and/or not setting a Field (in the index) to Required ... a field in the Index to be Null

mx

?