I'm not sure exactly how to best explain this, bet here it goes...

If I add a new record on a form and enter a duplicate SSN (the primary key), I want to be prompted that that SSN already exists once the user tabs away from the field. However, Access will accept the entry and let the user continue to enter data, and will only prompt what it's a duplicate when the record is saved or exited.

Can Access notify the user of the duplicate earlier?

I would use a DLookup() or DCount() in the BeforeUpdate event of the control that holds the SSN. Then give the user a message and set Cancel = True to hold the user in the control.

I would use a DLookup() or DCount() in the BeforeUpdate event of the control that holds the SSN. Then give the user a message and set Cancel = True to hold the user in the control.

Thanks. I have looked up some examples of these and for the life of me I can not get them to work without getting some syntax error.

My table is named "Client Info" and the SSN field is "SSN". The form name is "Client Info" (Yea, I know).

I think that I need to enter the code as an Event Procedure in between:

Private Sub SSN_BeforeUpdate(Cancel As Integer)

End Sub

I am not sure exactly how to place the code. The SSN field is formated as text.

You are going to want something like:
Private Sub SSN_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("SSN", "[Client Info]", "[SSN] = '" & Me.SSN & "'")) Then
MsgBox "This is a duplicate SSN number"
Cancel = True
End If

End Sub
Here's a good reference for the DLookup() syntax.
http://www.mvps.org/access/general/gen0018.htm

you also need to check if if is a new record or not as you may change an existing SSN unique number to a duplicate of SSN that exists in another record.

this is how a check for duplicate referral dates and this assumes there is an autonumber as a primary key.

notice that the second DCount excluded the current record



If Me.NewRecord Then
strsql = "[Internal_ID]=" & Forms!frmMPI![Internal_ID] & " AND [Date of Referral] =#" & Format(Me![txtDate of Referral], "mm/dd/yy") & "# AND Deleted=0"
If Not IsNull(Me![txtDate of Referral]) Then
If DCount("[Auto ID]", "EPI", strsql) > 0 Then
MsgBox "This Referral Date already exists for this Client", vbCritical
Cancel = True
End If
End If
Else
If Not IsNull(Me![txtDate of Referral]) Then
strsql = "[Internal_ID]=" & Forms!frmMPI![Internal_ID] & " AND [Auto ID] <>" & Me![Auto ID] & " AND [Date of Referral] =#" & Format(Me![Date of Referral], "mm/dd/yy") & "# AND Deleted=0"
If DCount("[Auto ID]", "EPI", strsql) > 0 Then
MsgBox "This Referral Date already exists for this Client", vbCritical
Cancel = True
End If
End If
End If

Are SSNs unique? In the UK, our equivalent NI number is often thought to be unique, but it's not guaranteed - temporary numbers are issued that may duplicate. They can also be unknown or not exist, which is also a problem for a PK.