Validation Rules or After Update Function??

[scott04]

I have a table that SSN is the primary key. On my form i am trying to make
it so that after entering a social into the program it immediately gives me
an error message since we do not want duplicate records. Do i need to use an
after update function or a validation rule? Any help or suggestions is
appreicated.

 

[Jeff Boyce]

You did say "any help..."!

If you require your users to enter a complete SSN, then search to see if the
characters they entered already exist, then tell the user "Nope, you dope,
you got it wrong!", your users might consider the application "user-surley".

Another approach is to use a combobox to present all possible (existing)
values, and to use the LimitToList property and the NotInList event to
manage when a "new" one is entered.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[scott04]

Jeff
Thanks for the information. The reason I ask is because a user does not get
the error message of a duplicate primary key until they exit the application.
My only other though was to put a command button right next to the social
box that is a "save" command since once it saves the error message will pop
up. The social is the first box on the form and we do not want users to type
in the whole form and then find out at the end that they entered the wrong
SSN. Any thoughts on the command button?

 

[Jeff Boyce]

My previous suggestion still holds. You won't need a command button if you
use a combo box.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have a table that SSN is the primary key. On my form i am trying to make
it so that after entering a social into the program it immediately gives me
an error message since we do not want duplicate records. Do i need to use an
after update function or a validation rule? Any help or suggestions is
appreicated.

If the user is entering (what's intended to be) new data, and will be typing
in a new SSN in expectation that it does NOT exist in the table, then you can
put code in the BeforeUpdate event of the SSN textbox to check:

Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[SSN]", "[tablename]", "[SSN] = '" _
& Me!txtSSN & "'") Then
MsgBox "This SSN has already been entered!", vbOKOnly
Cancel = True
<do something else if appropriate, e.g. offer to go to that record>
End If
End Sub

If they'll usually be entering data for an existing SSN and only occasionally
adding new data, then Jeff's combo box suggestion is the way to go.

John W. Vinson [MVP]

 

[scott04]

John,
I have enetered the code in the design view of the ssn textbox under the
before update field and the code does not run. Here is the code i am running:
Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[SSN]", "[Consumers]", "[SSN] = '" _
& Me!txtSSN & "'") Then
MsgBox "This SSN has already been entered!", vbOKOnly
Cancel = True

End If
End Sub

I have a table that SSN is the primary key. On my form i am trying to make
it so that after entering a social into the program it immediately gives me
an error message since we do not want duplicate records. Do i need to use an
after update function or a validation rule? Any help or suggestions is
appreicated.

If the user is entering (what's intended to be) new data, and will be typing
in a new SSN in expectation that it does NOT exist in the table, then you can
put code in the BeforeUpdate event of the SSN textbox to check:

Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[SSN]", "[tablename]", "[SSN] = '" _
& Me!txtSSN & "'") Then
MsgBox "This SSN has already been entered!", vbOKOnly
Cancel = True
<do something else if appropriate, e.g. offer to go to that record>
End If
End Sub

If they'll usually be entering data for an existing SSN and only occasionally
adding new data, then Jeff's combo box suggestion is the way to go.

John W. Vinson [MVP]

 

[scott04]

Looks like i changed it a little and now is working

John,
I have enetered the code in the design view of the ssn textbox under the
before update field and the code does not run. Here is the code i am running:
Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[SSN]", "[Consumers]", "[SSN] = '" _
& Me!txtSSN & "'") Then
MsgBox "This SSN has already been entered!", vbOKOnly
Cancel = True

End If
End Sub

I have a table that SSN is the primary key. On my form i am trying to make
it so that after entering a social into the program it immediately gives me
an error message since we do not want duplicate records. Do i need to use an
after update function or a validation rule? Any help or suggestions is
appreicated.

If the user is entering (what's intended to be) new data, and will be typing
in a new SSN in expectation that it does NOT exist in the table, then you can
put code in the BeforeUpdate event of the SSN textbox to check:

Private Sub txtSSN_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[SSN]", "[tablename]", "[SSN] = '" _
& Me!txtSSN & "'") Then
MsgBox "This SSN has already been entered!", vbOKOnly
Cancel = True
<do something else if appropriate, e.g. offer to go to that record>
End If
End Sub

If they'll usually be entering data for an existing SSN and only occasionally
adding new data, then Jeff's combo box suggestion is the way to go.

John W. Vinson [MVP]