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.
In the BeforeUpdate event of your Form's SSN TextBox Field:
If Not IsNull(DLookUp("[myTableRecordIDFieldName]", "myTableName", "[myTableSSNfieldName] = " & Me.myFormSSNfieldName)) Then
MsgBox"The Social Insurance Number entered allready exists."
Cancel = True
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
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
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.