Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Allow Duplicates But Request Confirmation

    Hi
    I have a text box on a form whose control source refers to a field in a table which is set to Indexed: Yes (No Duplicates).
    What I want to do is change this to Indexed: Yes (Duplicates Ok), but have a message box or something similar appear on the form when a duplicate number is entered to confirm that this is correct.
    Can someone with more experience help ?
    Thanks
    McHooty

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Replacing Field1 with the actual name of your Field/Control, and Domain with the name of the Table or Query that holds the Field:

    If Field1 is Text

    Code:
    Private Sub Field1_BeforeUpdate(Cancel As Integer)
      If DCount("Field1", "Domain", "[Field1] = '" & Me.Field1 & "'") > 0 Then
       Resp = MsgBox("This Value Already Exists! Do You Wish to Add Anyway?", vbYesNo)
        If Resp = vbNo Then
         Cancel = True
        End If
       End If
      End Sub
    If Field1 is Numeric

    Code:
    Private Sub Field1_BeforeUpdate(Cancel As Integer)
      If DCount("Field1", "Domain", "Field1 = " & Me.Field1) > 0 Then
       Resp = MsgBox("This Value Already Exists! Do You Wish to Add Anyway?", vbYesNo)
        If Resp = vbNo Then
         Cancel = True
        End If
       End If
      End Sub
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2012
    Posts
    2

    Thanks

    Hi Missinglinq
    Worked first time, just as I needed
    Many Thanks
    McHooty

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Good luck on your project

    and

    Welcome to dBf!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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