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.

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


"mbparks" <(E-Mail Removed)> wrote in message
news:2E16B3FA-A1E0-4682-A636-(E-Mail Removed)...
> 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.
>