3 Field Duplicate Dcount HELPPPP!

[Krays23]

Hi Guys im really sorry to be posting this because I see so many people asking the same question! I haven't been able to use all your excellent advise and adapt to my DB how ever on this occasion so its prompted me to post my first thread so please bare with me.

OK so I have a tblManualRewardIncident in there I have 3 fields I want to have my staff in put these rewards via a frmRewardIncidentEntry However im concerned over another staff member in putting the same data so I want the form to highlight on update that a duplicate is there already so cancel the data entry.

The 3 fields that need to check and match are StrataID , DateOfIncident , EndDateOfIncident these are all in the tblManualRewardIncident and appear as buttons in the form frmRewardIncidentEntry the buttons are as follows
cboStrataID , txtDateOfIncident , txtEndDateOfIncident

I want the VBA to run when I CLICK another button on same form cboSQCDP

All 3 fields must match exactly to flag a issue.

StrataID , DateOfIncident , EndDateOfIncident from tblManualRewardIncident

If it doesn't find a match continue and add the inputs in these boxes to the table if it finds a match flag a message and delete the data from the form.

Can you guys help me out ive tried many variations of posts I have seen on here but cant seem to pull it off its stressing me out lost 2 days of my life over this

which no doubt you guys will solve in 2 posts lol

 

[CJ_London]

try something like

if dcount("*","tblManualRewardIncident","StrataID=" & me.StrataID & " AND DateOfIncident =#" & format(me.dateofincident,"mm/dd/yyyy") & "# AND EndDateOfIncident =#" & format(me.EndDateOfIncident,"mm/dd/yyyy") & "#") >0 then
    msgbox "Already entered"
end if

 

[Krays23]

Superb stuff works a treat thanks a lot sorry for late reply Friday Sat is the weekend here in UAE. One more thing how do I make it not let you carry on with the entry after it sees a duplicate? currently you press ok and you can carry on entering data? I want it to blank out the date box and make you enter a different value?

I currently have your code on click in txtEndDateOfIncident.

Thanks

Dan