Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Need to force a form to delete if duplicate entry

    I have a form that has AllowDeletions set to No - it's a data entry form that needs total control over the entered data, so Delete is not an option for the user......
    There is a control ACCOUNT that does not allow duplicates entries.
    I needed a way to warn the user if they were entering a duplicate so I have set this before update test:

    Private Sub ACCOUNT_BeforeUpdate(Cancel As Integer)
    If DCount("*", "ACCTTABLE21109", "ACCOUNT = '" & Me.ACCOUNT & "'") > 0 Then
    MsgBox Me.ACCOUNT & " already exists. DO NOT DUPLICATE. Return cold call sheet to salesman."
    Cancel = True
    End If

    End Sub

    When they get this warning, they cannot go any further in the record. The problem is the way you get stuck in the ACCOUNT field...the only option is to enter different text there. And you can't delete.

    I need a way to check for the duplicate entry, alert the user if True, and delete the new record they just created...all in one step

    Any ideas???????

    thansk so much!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    See if the Undo method in VBA help does what you want.
    Paul

  3. #3
    Join Date
    May 2010
    Posts
    601
    I agree with Paul.

    The code would be something like this:

    Code:
    Private Sub ACCOUNT_BeforeUpdate(Cancel As Integer)
    
    If DCount("*", "ACCTTABLE21109", "ACCOUNT = '" & Me.ACCOUNT & "'") > 0 Then
       MsgBox Me.ACCOUNT & " already exists. DO NOT DUPLICATE. Return cold call sheet to salesman."
       Cancel = True
       Me.Undo
    End If
    
    End Sub
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  4. #4
    Join Date
    Jun 2010
    Posts
    186
    thank you! it works perfectly!

  5. #5
    Join Date
    May 2010
    Posts
    601
    You're welcome.

    Glad Paul and I could assist.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •