Hey guys,
I have built an awards tracker that we have 6 different people inputing awards into. With this many people involved I have written an IF Dcount( statement to try to prevent duplicate entries. I have gotten it to do ALMOST what I want, but still havent got it perfect. The following is where I am at on the code...

If DCount("*", "tblAwardsLog", "[PO #] & LastName & DateInitiated = '" & Me.[PO #] & Me.LastName & Me.DateInitiated & "'") > 0 Then
DoCmd.CancelEvent
DoCmd.OpenForm "frmRecordExists"
End If

If DCount("*", "tblAwardsLog", "LastName & Left(FirstName,3) & Recommended & DateInitiated & Nz([PO #]) = '" & Me.LastName & Left(Me.FirstName, 3) & Me.Recommended & Me.DateInitiated & Nz(Me.[PO #]) & "'") > 0 Then
DoCmd.CancelEvent
DoCmd.OpenForm "frmRecordExists"
End If

If DCount("*", "tblAwardsLog", "LastName & Left(FirstName,3) & Recommended & DateInitiated = '" & Me.LastName & Left(Me.FirstName, 3) & Me.Recommended & Me.DateInitiated & "'") > 0 Then
DoCmd.CancelEvent
DoCmd.OpenForm "frmRecordExists"
Cancel = True
End If

The problem is if I don't add a [PO #] when I first add the record, then I try to go back to add a PO # it tells me the record already exists. The fields I need checked is [LastName], [Left(FirstName, 3)], [Recommended], [DateInitiated], [PO #]

would redesigning the tables to include a primary key which prevented duplicate rows be a smarter call?

Well healdem it is possible to have these show up many times, just not at the same time on the same record twice

?
proper data design should eliminate duplicates
if you cannot do that then youneed to look at your stateemtns
Im suspicious of
i woudl have expected it to be somethign simialr to:-
you may need to bracke the dateinitiated with # to denote its a date. you may also ned to specify the date as a US date mm/dd/yyyy
eg:-

Thanks healdem for your timely response, what I did was put..

This allowed it to check the fields necessary for duplicates, but only apply to new records instead of the ones I go back to edit with certain information.

are you creating a primary key which is the name, first 3 letters of the forename and dateinitiated as a single value in the DB....


I sincerely hope not......