Verifying Not a Duplicate in table before Allowing SaveRecord in Form for that Table

[JayRab]

For Example I have a piece of equipment with ID 8060. I have a form were they enter a maintenance request for 8060 and I want to check to make sure there isnt an Active/Open request already in the Table for 8060. I can have the equipment 8060 on the table numerous times with a Status of "Closed" but I only want 1 instance of 8060 with status of "Active/Open".



I tried an If statement with DCount() placed on AfterUpdate event procedure for the drop box to select the equipment. The Status is Automatically set to Active/Open on Form load. So if I select 8060 from my drop box, I want a messagebox to popup saying duplicate already exists or whatever.

If DCount("*","[Equipment Downtime]","Equipment='&me.Equipment&' and Status ='&me.Status&'")>0 then
MsgBox "There is already an Active Maintenance Request for this Equipment. Please Close existing Request before logging another!"
Cancel=true
end if

I get nothing when I run this code. No errors no stop no nothing.

thanks for any help.

[pbaldy]

The syntax is off:

DLookup Usage Samples

[JayRab]

Awesome thanks for the quick reply. Syntax has been my enemy here lately.

If DCount("*", "[Equipment Downtime]", "Equipment='" & Forms![Equipment DownTime]!Equipment & "' AND Status = '" & Forms![Equipment DownTime]!Status & "'") > 0 Then

that got it fixed.

[pbaldy]

Happy to help. It seems like Greek until one day it all falls into place and it seems easy. Welcome to the site by the way!