Code Duplicate problem

[scott04]

Hi I am using the code below and it works fine if i am editing an existing
record. My problem is if i create a new record then it ignores this code.
Where else does this code belong so i don't have a problem? Thanks
Private Sub case_no_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.Case_No) Or (Me.Case_No = Me.Case_No.OldValue) Then
Else
If Not IsNull(DLookup("ID", "tblLawsuitTracking", "ID = " & Me.ID))
Then
Cancel = True
strMsg = "Case number already exists" & vbCrLf & _
"Please either edit existing record, re-enter a different
case number, or press Esc to undo."
MsgBox strMsg, vbExclamation, "Duplicate value!"
End If
End If

End Sub

 

[Beetle]

Did you copy and paste this code from your db, or did you manually type in
your post (in which case maybe it's just a typo)?

You first If statement;

If IsNull(Me.Case_No) Or (Me.Case_No = Me.Case_No.OldValue) Then
Else

is not going to do anything at any time, because there is no code between
"Then" and "Else"

The second part;

If Not IsNull(DLookup("ID", "tblLawsuitTracking", "ID = " & Me.ID))
Then
Cancel = True
strMsg = "Case number already exists" & vbCrLf & _
"Please either edit existing record, re-enter a different
case number, or press Esc to undo."
MsgBox strMsg, vbExclamation, "Duplicate value!"
End If

is only going to fire if "ID" is not null. I don't know what ID represents
in your db, but if case_no comes before ID when you enter a new record, then
your code won't do anything.

If you provide some more info on what you are trying to accomplish, maybe
someone can offer a better solution.