Results 1 to 3 of 3

Thread: Help!!!!

  1. #1
    Join Date
    Mar 2004

    Exclamation Unanswered: Help!!!!

    I have this code in a SSN_Before Update event and it is supposed to detect duplicated entered for Social Security Numbers by the users in my application. The problem I'm running into is that it catches the THIRD duplicate rather than catching that second duplicate. Can someone help me with this on letting me know why this might be happening? Here is the code:

    If Not IsNull(DLookup("[NumberOfDups]", "SSN Duplicates", "[SSN Field] = " & Me.SSN)) Then
    Style = vbRetryCancel
    Title = "Duplicate SSN"
    Response = MsgBox("The Social Security Number entered already exists.", Style, Title)
    Cancel = 2
    If vbCancel = 2 Then
    End If
    End If

    This is really messing me up and I've been pounding on it for a while without prevail. Any help would be very much appreciated. Thanks

  2. #2
    Join Date
    Nov 2004
    why don't you just create an index on the field and choose "no duplicates"? No code is needed.

    Go to your table design, click on the field in question, look at the "general" properties tab at the bottom of the design dialog, and in indexed, select "yes (no duplicates)".

    As for what you're code is doing... I can't really tell without knowing what sort of table it is your querying and how it gets its values. The field "numberofDups" seems odd to me.

    I would expect something like
    not IsNull(DLookup("[SSN]","tablename","SSN=" & SSN)

    or better yet, something like...
    Dim rst As DAO.Recordset
    rst = Me.Recordset
    rst.findFirst=rst.FindFirst "SSN=" & SSN
    If not rst.EOF then dostuff

  3. #3
    Join Date
    Mar 2004
    That change of the index property works great and is much easier. Thanks for the help. Sometimes, it's those little things that you over look sometimes. Makes a person feel stupid but I guess that is what a board like this is for and we all miss those little things from time to time. Thanks again, efflux

Posting Permissions

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