triggers duplicate msg if change is made

  • Thread starter Thread starter deb
  • Start date Start date
D

deb

access 2003
mainform is f001Projectreview with subform f015KeyMilestones(PK ProjectID)

I have the below code in the subforms beforeUpdate event.
It is to prevent duplicates, however, when I try to edit the ActualDt in the
record it recognizes the record as a duplicate.
How can I make changes in the record without it thinking it is a dupe?

If ((Me.KeyMilestonesSubID) = 12 Or (Me.KeyMilestonesSubID) = 20) Then
strWhere = "([ProjectID] = " & Nz(Me.ProjectID, 0) & _
") AND ([KeyMilestonesSubID] = " & Nz(Me.KeyMilestonesSubID, 0)
& ")"
varResult = DLookup("[KeyMilestonesID]", "[t51KeyMilestones]",
strWhere)
If Not IsNull(varResult) Then
MsgBox "Duplicate Entry." & vbCrLf & vbCrLf & _
"There can only be one PM080 and PM670 per project.", vbExclamation,
"Duplicate entry"
Cancel = True
End If
Exit Sub
End If
 
Deb -

You want to look for 2 or more records already in the table (not including
the one you are working on). Try this (untested):

If ((Me.KeyMilestonesSubID) = 12 Or (Me.KeyMilestonesSubID) = 20) Then
strWhere = "([ProjectID] = " & Nz(Me.ProjectID, 0) & _
") AND ([KeyMilestonesSubID] <> " & Nz(Me.KeyMilestonesSubID, 0) & _
") AND ([KeyMilestonesSubID] in (12,20))"
varResult = DLookup("[KeyMilestonesID]", "[t51KeyMilestones]",
strWhere)
If varResult >= 2 Then
MsgBox "Duplicate Entry." & vbCrLf & vbCrLf & _
"There can only be one PM080 and PM670 per project.",
vbExclamation, "Duplicate entry"
Cancel = True
End If
Exit Sub
End If
 
Daryl S said:
Deb -

You want to look for 2 or more records already in the table (not including
the one you are working on). Try this (untested):

If ((Me.KeyMilestonesSubID) = 12 Or (Me.KeyMilestonesSubID) = 20) Then
strWhere = "([ProjectID] = " & Nz(Me.ProjectID, 0) & _
") AND ([KeyMilestonesSubID] <> " & Nz(Me.KeyMilestonesSubID, 0) & _
") AND ([KeyMilestonesSubID] in (12,20))"
varResult = DLookup("[KeyMilestonesID]", "[t51KeyMilestones]",
strWhere)
If varResult >= 2 Then
MsgBox "Duplicate Entry." & vbCrLf & vbCrLf & _
"There can only be one PM080 and PM670 per project.",
vbExclamation, "Duplicate entry"
Cancel = True
End If
Exit Sub
End If

--
Daryl S


deb said:
access 2003
mainform is f001Projectreview with subform f015KeyMilestones(PK
ProjectID)

I have the below code in the subforms beforeUpdate event.
It is to prevent duplicates, however, when I try to edit the ActualDt in
the
record it recognizes the record as a duplicate.
How can I make changes in the record without it thinking it is a dupe?

If ((Me.KeyMilestonesSubID) = 12 Or (Me.KeyMilestonesSubID) = 20)
Then
strWhere = "([ProjectID] = " & Nz(Me.ProjectID, 0) & _
") AND ([KeyMilestonesSubID] = " & Nz(Me.KeyMilestonesSubID,
0)
& ")"
varResult = DLookup("[KeyMilestonesID]",
"[t51KeyMilestones]",
strWhere)
If Not IsNull(varResult) Then
MsgBox "Duplicate Entry." & vbCrLf & vbCrLf & _
"There can only be one PM080 and PM670 per project.",
vbExclamation,
"Duplicate entry"
Cancel = True
End If
Exit Sub
End If
Click to expand...
Click to expand...
 
Back
Top