Detecting a duplicate record at runtime 1

[grobermatic]

I am currently working on a database which caclulates and stores performance figures for employees where I work.

Raw figures are input in to a subform after NAME, DATE WORKED, HOURS WORKED are input on the main form. All of which are stored in the same table.

I'm looking for a method of stopping the user from entering more than 1 date for the same employee.

My ideal result would be:

If the user inputs a date which already exists for the employee they have chosen, a msgbox informs them that the employee already has a record for that date, then gives the user a choice of either going back to the form and entering a different date or going to an alternative form and editing the original record for that date.

I've played around with SQL and VBA code and think that I'm getting close to something. I'v also looked at some of the other threads o nthis site which have been kinda useful but I'm having trouble applying them to my situation.

Any help would be greatly appreciated!

 

[KenReay]

Hi
You make the prime key on the table EmployeeId and Date (ie a compound key), to do this in table design select the EmployeeId, hold down the cntrl key select the Date, both rows should now be selected (highlighted), click Edit\Primarykey, little key symbol should appear before the two rows in your table design.

Now you cannot add a dupliacte Employee/Date combination to the table

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[grobermatic]

Many thanks for this simple yet effective solution to my problem.

I used your solution and trapped the error (3022) to achieve my original idea.

I presumed the solution would be far more complicated!

thanks again!