Results 1 to 6 of 6

Thread: Please HELP!!!

  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Unanswered: Please HELP!!!

    Ok, here goes my problem. I know that I'm missing something small but it's racking my brain. I've written an application and I need to check and see if there are duplicates of a Social Security Number(SSN) entered every time a user enters an SSN. I would just make it easy and set it as a primary key however I'm using this table with a few different forms and, of course, you cannot leave the primary key blank in any record. So I have developed a query to, theoretically, query the table every time the user enters the SSN and exits the field. My question is, how do I access this query in my code so that when the text box loses focus it runs this query and when there is more than one SSN it shows the user a message box that they have entered a duplicate? I've tried all sorts of ways and I cannot seem to get my code to properly access this query.

    Please HELP!!! I'm going insane.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    In the BeforeUpdate event of your Form's SSN TextBox Field:

    Code:
    If Not IsNull(DLookUp("[myTableRecordIDFieldName]", "myTableName", "[myTableSSNfieldName] = " & Me.myFormSSNfieldName)) Then
    MsgBox"The Social Insurance Number entered allready exists."
    Cancel = True
    Me.myFormSSNfieldName.SetFocus
    End If


  3. #3
    Join Date
    Mar 2004
    Posts
    51

    Re:Please Help

    CyberLynx,

    That works perfect. Thank you very much on that. I've been sitting here all day trying different variations of this, that and the other. That is greatly appreciated. THANK YOU!!!!

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    You are very much welcome. Good luck with your project.



  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

    Dlookups

    I'd be very very cautious about using dlookups, they seem great but come at a high performance hit. Used very occasionally they can be very effective. for the SSID I'd be tempted to define the row in the table as null and with a unique index,then trap the error to see if a duplicate SSID was entered. Depending on yiur user base you could omit the error trap as the message is (almost) self explanitory

  6. #6
    Join Date
    Mar 2004
    Posts
    51

    DLookUp

    Ok, I am using this DLookUp to search for duplicates in my application:

    If Not IsNull(DLookup("[NumberOfDups]", "SSN Duplicates", "[SSN Field] = " & Me.SSN)) Then
    ...
    End If

    and it works but for some reason it is catching the duplicate after the THIRD duplicate is made instead of the second. Can anyone see why this would be? I have the function being called in SSN_BeforeUpdate so I would think that this would be correct but i'm missing something.

    Thanks for the help

Posting Permissions

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