How to use VBA to get Duplicate record Dialogue in Subform where Yes = Delete a record

[Sue22]

Hi After a lot of struggling I managed to get this to work:

  Private Sub Module_Code_AfterUpdate()
     Dim strCriteria As String
     strCriteria = "Module_Code=" & Chr(34) & Me.Module_Code & Chr(34) & " AND Course=" & Chr(34) & Me.Course & Chr(34)
      If DCount("Module_Code", "tbl Stages", strCriteria) > 0 Then
     MsgBox ("You have already entered this module code")
     End If
 End Sub 

It is used to warn if a duplicate value for a Module is being added on a sub form. There are instances where there can be two modules (but they are rare).
What I want the Dialogue that warns of a duplicate record to ideally do is have a Yes/ no question which is : "You have already entered this module code, do you want to delete this record?" where if the User clicks Yes the current record on the sub form is deleted and if they Click no they can continue to update the remaining parts of the record. I think this should be straightforward but have been fiddling with it for ages and can't see the wood for the tress anymore!



Thanks

[orange]

The database software can detect duplicates. And you can check for this condition and, if found, you can display a custom message.
You start by adding a unique index or unique composite index to the field(s) involved in your table.
Then, in your error handler you check for an attempt to add a duplicate record.
And, if present, you intercept and add your own message.

Good luck.

[Sue22]

Sorry I think you have misunderstood the code I have works but I want to enhance it by presenting the inputter a choice of Keeping the duplicate if appropriate and deleting it if they were adding in error. There are situations for this data where a duplicate is acceptable so I don't want to go down the route of indexes and preventing duplicates. I just want the user to be able to click Yes to delete the record and NO to keep it, its just this bit I need to aad to the VBA.

[ranman256]

add an extra field, MARK.
when user checkmarks it, it can delete records with the mark using a delete query.

[Sue22]

That seems a bit laborious for people to do. Do I assume that what I wanted to do is not possible????

[orange]

It might be easier for readers to understand if you could elaborate on the conditions that make a duplicate a record to be kept. A common requirement of database applications is to ensure uniqueness; and prevent duplication/replication.

It would also be helpful to you and readers if you could build a logic tree of the If else conditions involved in what you are asking.

   if Record....
      else if......
         else if....

           accept the duplicate
   else 
           reject the duplicate
.......

[ranman256]

The code I gave you makes it possible.
or
let use pick a record then press DEL key. That works too.

[DrGUI]

Try the following:

If MsgBox ("You have already entered this module code, do you want to delete this record?", vbYesNo, "Warning") = vbYes Then
... delete current record
Else
... continue updating
End If