Add Yes/ No to Dialogue - When user clicks Yes delete Record when user clicks no keep

[Sue22]

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

 [FONT=courier new][SIZE=2]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 [/SIZE][/FONT]

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 so I don't want to prevent duplicates just a warning you are adding one. I could just leave it as it is but in instances where it is an error Ideally a click of a button would remove the erroneously created record so:

What I want the Dialogue 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?"
If the User clicks Yes the current record on the sub form is deleted. If the user clicks 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 trees anymore :banghead:

Thanks

 

[AOB]

This might get you started?...

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
 
        Select Case MsgBox ("You have already entered this module code, do you want to delete this record?", vbYesNo)
 
            Case vbYes
 
                ' Insert code to purge record
 
            Case vbNo
 
                ' Insert code to continue editing
 
        End Select
 
    End If
 
 End Sub

 

[arnelgp]

i suggest doing this on BeforeUpdate event of your control:

If DCount("Module_Code", "tbl Stages", strCriteria) > 0 Then
IF MsgBox ("You have already entered this module code, do you want to delete this record?", vbYesNo+vbQuestion, "Duplicate Record") = vbYes Then
Me.Undo
End If
End If

 

[AOB]

Or, alternatively, don't bind the form to a query or table, but instead use your own validation code and controls to check the quality of the entered information before updating it or appending it to your table(s). That way no record is actually created or updated until the form is completed and some form of 'submit' button is clicked. No dirty records. Just a suggestion...

 

[Sue22]

Thanks guys, used bits from both of you and it now works exactly as I want it to.

 

[AOB]

Great stuff, glad to help, best of luck with it!