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

Hi Experts,

I have a Tbl_Strucutre with 4 combo fields.  I need the records to be unique, however, all 4 fields do not need to be completed. I can't set the primary key to all 4 fields because some fields can be blank.  For example, the following entries would be valid.

Column1      Column2      Column3      Column4
cat      hat      sat      fat
cat      hat      sat      
cat      hat            
cat      run      sun      
dog      blog      hog      fog
dog      bark      

How do I keep the user from entering duplicate records in a form while including blanks?      
Newbie20Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try creating an index using the four fields

open table in design view
click on indexes

myIndex   Column1
              Column2
              Column3
              Column4  
cyberkiwiCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
Newbie20Author Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Is this what you did ?
Capture1.gif
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
Newbie20Author Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
This is what happens - see image:

mx
Capture1.gif

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you include a Default value for the fields included in the Index? This would stop the Null value issue, but may not be suitable for you particular dataset.
Rey Obrero (Capricorn1)Commented:
?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.