Check for duplicates with multiple criteria

[mbparks]

My table contains a field labled "Case Number" and another field labled "Date
Completed". I have created an input form and included code to check for
duplicate case numbers. A msgbox appears with a warning. I would like the
code to check for a duplicate case number and then check the "Date Completed"
field to see if it is blank. If it is blank I would like the existing
record to open. If the date completed field is not blank I would like to
continue entering data in the form to create a new record.
The code I am currently using is:

Private Sub Case_Number_BeforeUpdate(Cancel As Integer)
If DCount("*", "Copy of DIV 3 ICT Database", "[Case Number] = '" & Me![Case
Number] & "'") > 0 Then
MsgBox "This item already exists in the table."
Cancel = True
Me.Undo
End If
End Sub

How can I incorporate the changes?
Any help is greatly appreciated.

 

[Arvin Meyer [MVP]]

You just need to check if Date Completed is blank before doing the Case
Number check:

Private Sub Case_Number_BeforeUpdate(Cancel As Integer)

If Len(Me.[Date Completed] & vbNullString) = 0 Then

Exit Sub

Else

If DCount("*", "Copy of DIV 3 ICT Database", _
"[Case Number] = '" & Me![Case Number] & "'") > 0 Then

MsgBox "This item already exists in the table."
Cancel = True
Me.Undo
End If

End If

End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access 2010 Solutions", published by Wiley