Assuming what Lord Kelvan gave as a sub is what you are looking for, he
simply forgot to add a closing ' at the end of the DLookUp criteria. Try

Private Sub Case_Number_BeforeUpdate(Cancel As Integer)
On Error GoTo Error_Handler

If IsNull(DLookup("[Case Number]", "[Copy of DIV 3 ICT Database]", _
"[Case Number] = '" & Me.[Case Number] & "'")) Then
DoCmd.GoToRecord , , acNewRec
Else
If IsNull(DLookup("[Date Completed]", "[Copy of DIV 3 ICT
Database]", _
"[Case Number] = '" & Me.[Case Number] & "'")) Then
Me.Filter = "id = 2"
Me.FilterOn = True
Me.Requery
Else
Cancel = True
Me.Undo
End If
End If

Error_Handler_Exit:
On Error Resume Next
Exit Sub

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: Case_Number_BeforeUpdate" & vbCrLf
& "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub

Also, I strongly encourage you to use proper code indenting and most
importantly error handling.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"mbparks" wrote:

I'm still having a problem. I copied your code but still having problems.
The code window opens and the first line of code is highlighted.
Please help.

"Lord Kelvan" wrote:

This code should do what you want

Also as a note for future reference don’t use spaces in any table name
form name field in a table anything as it creates problems when
programming use camel case like this CopyOfDIV3ICTDatabase

You forgot to put [] around Copy of DIV 3 ICT Database because of the
spaces in your code

Private Sub Case_Number_BeforeUpdate(Cancel As Integer)
If isnull(Dlookup("[Case Number]","[Copy of DIV 3 ICT Database]",
"[Case Number] = '" & [Case
Number].value)) Then
DoCmd.GoToRecord , , acNewRec
else
If isnull(Dlookup("[Date Completed]","[Copy of DIV 3 ICT Database]",
"[Case Number] = '" & [Case
Number].value)) Then
Me.Filter = "id = 2"
Me.FilterOn = True
Me.Requery
else
Cancel = True
Me.Undo
End If
end if
End Sub

Hope this helps

Regards
Kelvan
.