How not allow a new record to be added with the same Date and Employee ID as any previous records

I have a form which is a continuous form.  On the form is a date field and an EmployeeID combobox field.  The user may enter "9/28/2015" and "John Doe" for example. The bound field for the EmployeeID combo box is the PK number for that employee.   But if for example they have already entered a "9/28/2015" and "John Doe" and they try to enter another "9/28/2015" and "John Doe" , I want a message to appear letting them know they already entered "9/28/2015" and "John Doe" and not allow them to enter another one.

How can I do this?
SteveL13Asked:
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.

PatHartmanCommented:
In the table design, create a unique index that includes the EmployeeID and the date.

Access will prevent a duplicate record from being saved regardless of how the save is initiated.  In your form, you might want to trap the error and make it more user friendly (use the On Error event) or you can proactively look for the duplicate values in the Beforeupdate event and cancel the event if you determine that the record already exists.  I'm not sure what error code will be raised so if you want to use the On Error event (my choice since it is most efficient), then add a message box and try to add a duplicate.  When the message box pops up, stop the code and print the error number in the debug window.  Then add code to trap for that specific error message and cancel the Access error message.
Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
        Case ????
            Msgbox "This employee record already exists and cannot be added again.",vbokOnly
            Response = acDataErrContinue
        Case Else
            MsgBox "Please report this error to your technical support person.", vbOKOnly
            Response = acDataErrDisplay
    End Select
            End Sub

Open in new window

0

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
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.