DLookup to validate fields

[Lissa]

I was wondering if anyone could point me in the right direction...I'm surprised I got this far by fumbling though...

I am trying to prevent users from entering a new record in the database if it already exists. I am looking at 2 particular fields - project and name.

If (Not IsNull(DLookup("Project", "qryExistingEntries", "Project = Forms![frmSummarizedActualRsrcData]!Project"))) Then
If (Not IsNull(DLookup("Name", "qryExistingEntries", "Name = Forms![frmSummarizedActualRsrcData]!Name"))) Then

MsgBox "An entry against this resource name and project already exists. Please update the existing entry.", vbCritical, "Warning!"
Me.Undo
End If
End If

It works...sort of. How can I associate both fields as one record rather than treating them independently? Right now, if there is any matching project and any matching name entry - it pops up the warning. I want to verify if one single record has a matching project and name. Do I have to concatenate the fields first then check them? There must be an easier way, don't ya think?

Anybody's input is greatly appreciated.
Thanks

 

[spasticus]

try putting the two if criteria in one if ... so...

StrProject = DLookup("Project", "qryExistingEntries", "[Project] = """ & Forms![frmSummarizedActualRsrcData]!Project & """")
StrName = DLookup("Name", "qryExistingEntries", "[Name] = """ & Forms![frmSummarizedActualRsrcData]!Name & """")

If Not IsNull StrProject and Not IsNull StrName then
MsgBox "An entry against this resource name and project already exists. Please update the existing entry.", vbCritical, "Warning!"
Me.Undo
End If

 

[Lissa]

Thanks for the reply... I tried what you said
If (Not IsNull(StrProject) And (Not IsNull(StrName))) Then
it works but I think I still have the logic wrong.

<sigh> I'll keep trying.

 

[WayneRyan]

Lissa,

If DCount("[Project]", _
          "qryExistingEntries", _
          "[Project] = '" & Forms![frmSummarizedActualRsrcData]!Project & "' And " & _
          "[Name] = '" & Forms![frmSummarizedActualRsrcData]!Name & "'") > 0 Then
    MsgBox "An entry against this resource name and project already exists. Please update the existing entry.", vbCritical, "Warning!"
    Me.Undo
End If

Wayne

 

[Lissa]

Thank you Wayne!! It works...
I never thought about using DCount

 

[WayneRyan]

Lissa,

Glad to hear it works.

The DCount is much easier than DLookUp because it will never return Nulls.

See ya,
Wayne