Prevent Duplicate Entry Under an Employee Record for PTO Dates (1 Viewer)

Hello,

I tried to find something on this but could not locate it. I have a PTO Database and a form in which a user searches for the employee name (Main form) and then a Sub-Form which is tied to the PTO table and allows the user to enter the date of PTO for that Employee-the forms are joined on Employee ID. Is there a way to prevent the User from adding the same date in under that Employee multiple times?

Thanks for any help.

Lilly

Could set compound index in table design and let Access nag users.

Can use code that validates user input.

Perhaps you could adapt this...

Appointment Collision Checker
https://access-diva.com/blog/?p=397

This is a pretty simple situation that as June suggested, a compound index will solve. To create a compound index isn't so obvious though.

Open the table in design view.
Open the indexes dialog.
Go to the first entirely empty row.
Create a name for the index.
Choose the first column.
Set the properties to unique
Go to the next row.
Skip over the name column.
Choose the second field.
Save the table.

If there is any invalid data in the table, you will get an error. You will need to clean up the duplicates BEFORE you will be allowed to create the unique index.

Just FYI, Access (Jet and ACE) allow a maximum 0f 10 columns in a compound index. All indexes require a name so you will see at least one index already created and that will be the primary key. You may also see other indexes that you created by selecting the indexed property in table design view. Access uses the index name as the "header" for an index. subsequent lines with blank index names will be assumed to be part of the last named index.

Hi all, thank you so much for all the help (as always). You pointed me in the right direction, I found an article on-line that walked me through indexing in a little more detail as I have never done this before and it worked.

Here is the link incase any one else has the same issue, it was very detailed.

http://www.geeksengine.com/article/composite-unique-index-access.html

Lilly