check three fields for duplicate record and give warning

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

deb

I have a main form called f001ProjectReview(ProjectID PK) and a subfrom
called f015KeyMilestones

On the subform, If user tries to enter a record with the same data in the
three fields (ProjectID, UnitNo and KeyMilestonesSubID) creating a duplicate.
I need to warn the user.

Record is a duplicate for this unit and milestone. Are you sure you want to
create this duplicate?
If yes, create it and if no, remove it.

How can I do this??
 
I have a main form called f001ProjectReview(ProjectID PK) and a subfrom
called f015KeyMilestones

On the subform, If user tries to enter a record with the same data in the
three fields (ProjectID, UnitNo and KeyMilestonesSubID) creating a duplicate.
I need to warn the user.

Record is a duplicate for this unit and milestone. Are you sure you want to
create this duplicate?
If yes, create it and if no, remove it.

How can I do this??
Click to expand...

Ensure that all data entry is done using a Form - tables don't have any
programmable events, and the best you could do is create a unique three-field
index which would absolutely prohibit duplicates (with no "warning only"
option).

Use the Form's BeforeUpdate event with code like:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Not IsNull(DLookUp("[KeyMilestonesSubID]", "[tablename]", _
"[ProjectID] = " & Me!ProjectID & _
" AND [UnitNo] = " & Me!UnitNo & _
" AND [KeyMilestonesSubID] = " & Me!KeyMilestonesSubID) Then
iAns = MsgBox("Record is a duplicate... <your text>", vbYesNo)
If iAns <> vbYes Then
Cancel = True
End If
End If
End Sub
 
In the forms BeforeUpdate event;

Private Sub Form_BeforeUpdate (Cancel As Integer)

Dim strLookup As String, strMsg As String

strLookup = Dlookup("*", "tblYourTable", "ProjectID=" & Me!ProjectID _
& " And UnitNo=" & Me!UnitNo & " And KeySubID=" _
& Me!KeySubID)

strMsg = "Duplicate record. Do you want to continue?"

If Me.NewRecord Then
If Not IsNull(strLookup) Then
If MsgBox(strMsg, vbYesNo, "Duplicate")=vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

End Sub


I have assumed that the three fields in question are all numeric data types.
 
Back
Top