Duplicate values problem

[Misty]

I have a table with the following fields.

Record Number (Autonumber and Primary Key)
Work Order Number (Text)
Work Date (short date)
EmpNum (text)
RegTime (number-single)
OT (number-single)
VacHrs (number-single)
SickHrs (number-single)

A record CAN have a duplicate work order number.

I want no duplicates of the combination of:

Work Order Number
Work Date
EmpNum

If a duplicate record containing the three fields shown above is entered, I want to have a msgbox come up with the message:
---Msgbox---
"You have attempted to enter duplicate information involving the following fields:

Work Order Number
Work Date
EmpNum

Please search the WO records for the duplicate information and adjust if necessary.
---End of Msgbox---

I've tried to find the answer in the A2k help, but I'm getting stumped.

Can someone tell me how to do this?

 

[KenHigg]

Would it work to set these up as a composite primary key?

ken

 

[maxmangion]

Would it work to set these up as a composite primary key?

I do not believe that it would be the right approach using 3 fields as a primary key just to avoid duplication.

If you search the forum, there are several threads covering this problem and see which approach suits you best.

One way of doing it, would be to create a field in a query which concatenates all of your three fields:

Expr1:[field1] & ", " & [field2] & ", " & [field3]

then do a dcount and if the result would be > 1 alert the message box!

 

[Misty]

I've tried a search within the forum and didn't get any appropriate postings as a result.

What criteria did you use as a search?

I tried "duplicate record"

 

[maxmangion]

try duplicate values

 

[joeselch]

Not sure which version of Access you're using, but the Access 2000 Help file has two topics that should be of interest to you:

Prevent duplicate values from being entered in a combination of fields
Create a multiple-field index

To prevent duplicate values in a combination of fields, create a multiple-field index.
These Help items explain how.