Duplicate catch needs to remove row not field (1 Viewer)

dgreen

Member
Local time
Today, 00:32
Joined
Sep 30, 2018
Messages
397
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?
1585624969977.png


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
 

Attachments

  • Duplicate_Issue.zip
    70.7 KB · Views: 271

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
Hi. Me.Undo should do it; but sometimes, you may have to do it twice. Also, if you don't want the record saved, you need to add Cancel=True to your code.
 

Micron

AWF VIP
Local time
Today, 01:32
Joined
Oct 20, 2018
Messages
3,476
You need to Cancel the event (note the Cancel As Integer option). So maybe
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.")
     Cancel = True
     Me.Undo
End If
End Sub
I think you can simplify this part by nesting quotes:
[Relationship]= ' " & Me![Relationship] & " ' ")) > 0
I added spaces to make the single quotes more obvious. Note that by using Me! instead of Me. you are using late bound compiling of those references. If you misspell Relationshp it won't fail until the code runs and reaches that line. You will not uncover such errors by compiling your code either. Last, not incorrect, but if you don't have spaces in names (or Heaven forbid, start a name with a number) you don't really need the square brackets.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:32
Joined
May 7, 2009
Messages
19,169
on the subform's code, I added a variable, lngRecordToDelete, which will hold the record number (autonumber) of the
record to delete.

also I added code to the subform's Timer Event.

see the code and test.
 

Attachments

  • Duplicate_Issue.zip
    50.9 KB · Views: 270

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:32
Joined
May 7, 2009
Messages
19,169
on New record, yes, cancel will do.
on existing record it will not. see image of post #1.
 

Micron

AWF VIP
Local time
Today, 01:32
Joined
Oct 20, 2018
Messages
3,476
OK. I simply selected the record and hit delete. That's what record selector are for - no code needed + much simpler. Sometimes people just need to learn how to use program features rather than programming a feature.
 

dgreen

Member
Local time
Today, 00:32
Joined
Sep 30, 2018
Messages
397
I'm both the programmer and primary user and I'm not remembering the nuances. It helps me, sometimes, to have automation help me to the extent possible. And working through some of these VBA techniques is hopefully making me a little smarter.

Point taken on the other approach.

Sometimes people just need to learn how to use program features rather than programming a feature.
 

dgreen

Member
Local time
Today, 00:32
Joined
Sep 30, 2018
Messages
397
@Micron

How do you get the ' " to work? Below is erroring. Both the System_Acronym and System_Name are text fields.

Code:
If DCount("*", "[t_Systems]", "[System_Acronym]= '" & Nz(Me![System_Acronym], "") & "'" And [System_Name] = '" & Me![System_Name] & "'") > 0 Then

I think you can simplify this part by nesting quotes:
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
@Micron

How do you get the ' " to work? Below is erroring. Both the System_Acronym and System_Name are text fields.

Code:
If DCount("*", "[t_Systems]", "[System_Acronym]= '" & Nz(Me![System_Acronym], "") & "'" And [System_Name] = '" & Me![System_Name] & "'") > 0 Then
Hi. I pointed someone to this article earlier. Maybe it might help?

 

dgreen

Member
Local time
Today, 00:32
Joined
Sep 30, 2018
Messages
397
Closer, I hope - Error Type 13 mismatch.
Code compiles. Both fields are Short Text format.

Code:
If DCount("*", "[t_Systems]", "[System_Acronym]= '" & Me![System_Acronym] & "'" And "[System_Name] = '" & Me![System_Name] & "'") > 0 Then
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
Remove the double quote immediately before and after the word And.
 

dgreen

Member
Local time
Today, 00:32
Joined
Sep 30, 2018
Messages
397
It's working. Thanks.

Code:
If DCount("*", "[t_Systems]", "[System_Acronym]= '" & Me![System_Acronym] & "' And [System_Name] = '" & Me![System_Name] & "'") > 0 Then
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
It's working. Thanks.

Code:
If DCount("*", "[t_Systems]", "[System_Acronym]= '" & Me![System_Acronym] & "' And [System_Name] = '" & Me![System_Name] & "'") > 0 Then
Good job! Good luck with your project.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:32
Joined
May 7, 2009
Messages
19,169
OK. I simply selected the record and hit delete. That's what record selector are for - no code needed + much simpler. Sometimes people just need to learn how to use program features rather than programming a feature.
if it is that simple, which the OP already knows, we won't be having this discussion, would we? see post #1 for the Request.
 

Users who are viewing this thread

Top Bottom