Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2011
    Posts
    413

    Unanswered: Text Field No Duplicates

    I have a field in my table named GirlID which is set to Text for the Data Type but I wish it to not allow duplicates. A typical input for this field on my form is P352947 How can I do this ? I already have a primary key set on the table.

    Thanks,,,

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Code:
    Private Sub GirlID_BeforeUpdate(Cancel As Integer)
    
     If DCount("GirlID", "YourTableName", "[GirlID] = '" & Me.GirlID & "'") > 0 Then
     
      MsgBox "This GirlID Already Exists!"
      
      Cancel = True
      
     End If
    
    End Sub

    Assuming that GirlID is the Name of your Textbox on the Form, as well as the Field Name in the Table. Just replace YourTableName with the actual name of yours.

    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
    Nov 2011
    Posts
    413

    Duplicates

    Any way to get rid of the popup yes/no since I do NOT want a duplicate at all ?
    Private Sub GirlID_AfterUpdate()
    If DCount("GirlID", "tbl_main", "[GirlID] = '" & Me.GirlID & "'") > 0 Then

    Resp = MsgBox("This Girl Already Exists! Do You Wish to Add Anyway?", vbYesNo)

    If Resp = vbNo Then
    Cancel = True

    Me.GirlID.SetFocus
    Me.Undo


    End If

    Girl = StrConv(Girl, vbProperCase)

    End If

    End Sub

  4. #4
    Join Date
    Jul 2012
    Posts
    8
    Any reason why this field cannot be indexed without duplicates allowed?

  5. #5
    Join Date
    Nov 2011
    Posts
    413
    NO, however I want it to check for duplicates before I go thru entering all the data on the form.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Burrina View Post
    Any way to get rid of the popup yes/no since I do NOT want a duplicate at all ?
    Sorry, I re-read your post when I was checking my final answer, after 'sending' it, saw that I needed to modify the code (which was some boilerplate I had used before) and edited the code. As it appears now, I think it meets your requirement.

    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

  7. #7
    Join Date
    Nov 2011
    Posts
    413
    How can I make it set focus back on the GirlID using this code ? I of course tab to net field after entering data and error message works and it checks for dup's but then tabs to next field and I want it to go back to the first field which is GirlID

    If DCount("GirlID", "tbl_main", "[GirlID] = '" & Me.GirlID & "'") > 0 Then

    MsgBox "This Girl Already Exists! Try Again?"



    Me.Undo
    Me.GirlID.SetFocus

    End If

    Girl = StrConv(Girl, vbProperCase)

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Burrina View Post
    How can I make it set focus back on the GirlID using this code?
    By using the revised code I gave you in Post #2!

    Cancel = True not only cancels the update of the Field, but causes the cursor to remain in the Field.

    Why are you trying to use Me.Undo? If the user has filled in any other data, in the Record, before entering a duplicate GirlID, Me.Undo will remove all of that data, not just the GirlID! And trust me, you have no idea in what order users will enter data. You don't even have any idea in what order you will enter the data! A bit of data is missing, so you start to enter the other data, then go to find the missing stuff.

    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

  9. #9
    Join Date
    Nov 2011
    Posts
    413
    Thanks, I really appreciate it. Maybe I will be able to help some poor soul one day as well.

  10. #10
    Join Date
    Jul 2012
    Posts
    4

    No Dup

    You should set the field GirlId Properties with Index (yes and No Duplicate);
    then you can use BeforeUpdate Even on the Form to check to be sure you are not enter duplicate value before you update that field!

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If you want something like an ID to be unique, what you're really saying is that you only want one Record with that ID. Do you really want a user to enter an entire Record, only to discover, when going to Save the Record, that it already exists and that they've just wasted their time? Of course not! You want them to find out before wasting their time! Which is exactly what Burrina said in Post #5, after someone else suggested using Indexed/No Dups!

    For this reason, both setting the Field to Indexed/No Dups and checking for dups in the Form_BeforeUpdate event is incorrect.

    Linq ;0)>
    Last edited by Missinglinq; 08-06-12 at 13:28.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    Nov 2011
    Posts
    413
    So then it's impossible to sort records in whatever I want ? I cant assign a value to a record and then have all the records on that form sort in the order I chose ? I gave Mary a 1 and Lisa a 2 so Mary shows up first on the form and so on ? And if I need to re-sort them, I cant, just asking ? Maybe what I am trying to do cant be done !

  13. #13
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Burrina, I believe you've become confused! This appears to be a response intended for another thread you have open, not this one!

    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
  •