Code Duplicate problem

  • Thread starter Thread starter scott04
  • Start date Start date
S

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
 
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
Click to expand...

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
Click to expand...

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.
 
Back
Top