As a simple demo of my issue..... (attached document)
The system to project sub form can hold a unique system to project to relationship (System 1 + Supplier OR System 1 + Buyer OR System 1 + Blank value).
But when the Relationship value is populated (red circle) with either Supplier or Buyer in this example for System 1, there should be an error message (happens) and then the entire row should be removed (not happening).
What's happening is just the value that was selected in the Relationship field is being removed. So it returns to the below appearance.
How to change the (Me.Undo) code below to remove the entire record when it becomes a duplicate record?
The system to project sub form can hold a unique system to project to relationship (System 1 + Supplier OR System 1 + Buyer OR System 1 + Blank value).
But when the Relationship value is populated (red circle) with either Supplier or Buyer in this example for System 1, there should be an error message (happens) and then the entire row should be removed (not happening).
What's happening is just the value that was selected in the Relationship field is being removed. So it returns to the below appearance.
How to change the (Me.Undo) code below to remove the entire record when it becomes a duplicate record?
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "[t_System_Project_Relationship]", "[Project_ID]= " & Me.[Project_ID] & " and [System_ID]= " & Me.[System_ID] & " And [Relationship]= " & Chr(34) & Me![Relationship] & Chr(34)) > 0 Then
MsgBox ("This is a duplicate record. Click OK to remove it.")
Me.Undo
End If
End Sub