Error Message with Duplicate Values

[Sandy Burgess]

I have a table called Patient Data that contains Social Security Numbers. –
the field is called SSN I do not want duplicate SSN’s in the database,
On my input form, I have the following event procedure on the Before Update
line

Private Sub textSSN_BeforeUpdate(Cancel As Integer)
If SSN = DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN & "'") Then
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If
End Sub


The message box with “SSN exists. Verify SSN†comes up. So far so good.
Then when I click ok, I get an Access Popup that states “The value in the
field or record violates the validation rule for the record or field ………â€

How can I get rid this second popup? This message will confuse the casual
user. Thanks for your help.


Sandy

 

[Dale Fye]

Sandy,

Try:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

If ISNULL(DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN & "'"))
Then
'do nothing, no match
ELSE
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

HTH
Dale

 

[Brian]

A couple of things here.

1. Note that your current code will indicate a duplicate if you enter change
the SSN for the current patient, then change it back (without closing the
record in between); the code will find the SSN in the table and assume the
one input is a duplicate. This code will prevent that:

If DCount("[SSN]", "[Patient Data]", "[SSN] = '" & textSSN & "' And
PatientID <> " & PatientID) > 0 Then....

2. Do you have any validation rules in effect for the control? With the
above code, it should work without triggering an actual dupliate error.

3. One further suggestion: in the table, you could set the SSN field as
Indexed (No Duplicates). This way, you can let the underlying table do the
work of checking for you and, in fact, prevent duplicates even if you were to
enter directly into the table rather than through the form. You could still
maintain the code above, or you could opt to trap the duplicate key error
(however, then you have to trap the duplicate key error and present a nice
MsgBox to the user instead of the system duplicate key message).

 

[Sandy Burgess]

Dale, I tried your code. I get a Compile error: syntax error. I don't write
code - I let Access do it for me - so I don't know how to fix this. The If
is null...........Then is in red and the 'do nothing, no match is in green if
that helps.

Thanks for the quick response.
--
Sandy Burgess

Sandy,

Try:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

If ISNULL(DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN & "'"))
Then
'do nothing, no match
ELSE
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a table called Patient Data that contains Social Security Numbers. –
the field is called SSN I do not want duplicate SSN’s in the database,
On my input form, I have the following event procedure on the Before Update
line

Private Sub textSSN_BeforeUpdate(Cancel As Integer)
If SSN = DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN & "'") Then
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If
End Sub


The message box with “SSN exists. Verify SSN†comes up. So far so good.
Then when I click ok, I get an Access Popup that states “The value in the
field or record violates the validation rule for the record or field ………â€

How can I get rid this second popup? This message will confuse the casual
user. Thanks for your help.


Sandy

 

[Dale Fye]

Sandy,

My guess is that you copied the text exactly as it appears in your news
browser, and that the word wrap goofed that up. The Then should appear at
the end of the line which starts IF ISNULL....

Brian brings up a good point, is this form only used for entering new users,
or is it also used for editing information on old users? If it is used for
both of these purposes, then this will generate an error for all of your old
users. Assuming you have a PatientID that is not the [SSN] (I believe this
is a legal requirement for all medical databases), I would modify the code
like:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria as string

strCriteria = "[SSN] = '" & me.txtSSN & "' AND " _
& "[PatientID] <> '" & me.txtPatientID & "'"
If ISNULL(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
ELSE
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale, I tried your code. I get a Compile error: syntax error. I don't write
code - I let Access do it for me - so I don't know how to fix this. The If
is null...........Then is in red and the 'do nothing, no match is in green if
that helps.

Thanks for the quick response.
--
Sandy Burgess

Sandy,

Try:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

If ISNULL(DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN & "'"))
Then
'do nothing, no match
ELSE
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a table called Patient Data that contains Social Security Numbers. –
the field is called SSN I do not want duplicate SSN’s in the database,
On my input form, I have the following event procedure on the Before Update
line

Private Sub textSSN_BeforeUpdate(Cancel As Integer)
If SSN = DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN & "'") Then
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If
End Sub


The message box with “SSN exists. Verify SSN†comes up. So far so good.
Then when I click ok, I get an Access Popup that states “The value in the
field or record violates the validation rule for the record or field ………â€

How can I get rid this second popup? This message will confuse the casual
user. Thanks for your help.


Sandy

 

[Sandy Burgess]

There are 2 forms. One for new patients and one to edit existing. Let's
work on the edit existing form.

I am getting a Visual basic err. Run time error '3464' Data Type Mismatch
in crieria expression.

I click on debug and the line "If IsNull(DLookup("SSN", "Patient Data",
strCriteria)) Then" is in yellow. This is typed all on one line. The
actual code entered is :

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> '" & Me.textID & "'"
If IsNull(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

Sandy Burgess

Sandy,

My guess is that you copied the text exactly as it appears in your news
browser, and that the word wrap goofed that up. The Then should appear at
the end of the line which starts IF ISNULL....

Brian brings up a good point, is this form only used for entering new users,
or is it also used for editing information on old users? If it is used for
both of these purposes, then this will generate an error for all of your old
users. Assuming you have a PatientID that is not the [SSN] (I believe this
is a legal requirement for all medical databases), I would modify the code
like:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria as string

strCriteria = "[SSN] = '" & me.txtSSN & "' AND " _
& "[PatientID] <> '" & me.txtPatientID & "'"
If ISNULL(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
ELSE
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale, I tried your code. I get a Compile error: syntax error. I don't write
code - I let Access do it for me - so I don't know how to fix this. The If
is null...........Then is in red and the 'do nothing, no match is in green if
that helps.

Thanks for the quick response.
--
Sandy Burgess

Sandy,

Try:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

If ISNULL(DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN & "'"))
Then
'do nothing, no match
ELSE
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a table called Patient Data that contains Social Security Numbers. –
the field is called SSN I do not want duplicate SSN’s in the database,
On my input form, I have the following event procedure on the Before Update
line

Private Sub textSSN_BeforeUpdate(Cancel As Integer)
If SSN = DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN & "'") Then
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If
End Sub


The message box with “SSN exists. Verify SSN†comes up. So far so good.
Then when I click ok, I get an Access Popup that states “The value in the
field or record violates the validation rule for the record or field ………â€

How can I get rid this second popup? This message will confuse the casual
user. Thanks for your help.


Sandy

 

[Dale Fye]

Sandy,

Is your ID field text or numeric? By the error message I would assume
numeric, and I should have assumed that in my original code. When the field
you are trying to compare to is numeric, you don't need to wrap it in quotes
the way I originally did. Take a close look at the original code and the
new code provided below. You will see that I dropped a ' right after the >
and deleted the stuff after textID

Change strCriteria to:

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> " & Me.textID

HTH
Dale

There are 2 forms. One for new patients and one to edit existing. Let's
work on the edit existing form.

I am getting a Visual basic err. Run time error '3464' Data Type
Mismatch
in crieria expression.

I click on debug and the line "If IsNull(DLookup("SSN", "Patient Data",
strCriteria)) Then" is in yellow. This is typed all on one line. The
actual code entered is :

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> '" & Me.textID & "'"
If IsNull(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

Sandy Burgess

Sandy,

My guess is that you copied the text exactly as it appears in your news
browser, and that the word wrap goofed that up. The Then should appear
at
the end of the line which starts IF ISNULL....

Brian brings up a good point, is this form only used for entering new
users,
or is it also used for editing information on old users? If it is used
for
both of these purposes, then this will generate an error for all of your
old
users. Assuming you have a PatientID that is not the [SSN] (I believe
this
is a legal requirement for all medical databases), I would modify the
code
like:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria as string

strCriteria = "[SSN] = '" & me.txtSSN & "' AND " _
& "[PatientID] <> '" & me.txtPatientID & "'"
If ISNULL(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
ELSE
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale, I tried your code. I get a Compile error: syntax error. I don't
write
code - I let Access do it for me - so I don't know how to fix this.
The If
is null...........Then is in red and the 'do nothing, no match is in
green if
that helps.

Thanks for the quick response.
--
Sandy Burgess


:

Sandy,

Try:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

If ISNULL(DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN &
"'"))
Then
'do nothing, no match
ELSE
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a table called Patient Data that contains Social Security
Numbers. -
the field is called SSN I do not want duplicate SSN's in the
database,
On my input form, I have the following event procedure on the
Before Update
line

Private Sub textSSN_BeforeUpdate(Cancel As Integer)
If SSN = DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN &
"'") Then
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If
End Sub


The message box with "SSN exists. Verify SSN" comes up. So far
so good.
Then when I click ok, I get an Access Popup that states "The value
in the
field or record violates the validation rule for the record or
field ..."

How can I get rid this second popup? This message will confuse the
casual
user. Thanks for your help.


Sandy

 

[Sandy Burgess]

Thank you so much Dale. Works the way I want it to. Now I'll work on the
input form. Hopefully I can get it to work on my own. If not, I'll be back
with questions.
--
Sandy Burgess

Sandy,

Is your ID field text or numeric? By the error message I would assume
numeric, and I should have assumed that in my original code. When the field
you are trying to compare to is numeric, you don't need to wrap it in quotes
the way I originally did. Take a close look at the original code and the
new code provided below. You will see that I dropped a ' right after the >
and deleted the stuff after textID

Change strCriteria to:

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> " & Me.textID

HTH
Dale

There are 2 forms. One for new patients and one to edit existing. Let's
work on the edit existing form.

I am getting a Visual basic err. Run time error '3464' Data Type
Mismatch
in crieria expression.

I click on debug and the line "If IsNull(DLookup("SSN", "Patient Data",
strCriteria)) Then" is in yellow. This is typed all on one line. The
actual code entered is :

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> '" & Me.textID & "'"
If IsNull(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

Sandy Burgess

Sandy,

My guess is that you copied the text exactly as it appears in your news
browser, and that the word wrap goofed that up. The Then should appear
at
the end of the line which starts IF ISNULL....

Brian brings up a good point, is this form only used for entering new
users,
or is it also used for editing information on old users? If it is used
for
both of these purposes, then this will generate an error for all of your
old
users. Assuming you have a PatientID that is not the [SSN] (I believe
this
is a legal requirement for all medical databases), I would modify the
code
like:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria as string

strCriteria = "[SSN] = '" & me.txtSSN & "' AND " _
& "[PatientID] <> '" & me.txtPatientID & "'"
If ISNULL(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
ELSE
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Dale, I tried your code. I get a Compile error: syntax error. I don't
write
code - I let Access do it for me - so I don't know how to fix this.
The If
is null...........Then is in red and the 'do nothing, no match is in
green if
that helps.

Thanks for the quick response.
--
Sandy Burgess


:

Sandy,

Try:

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

If ISNULL(DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN &
"'"))
Then
'do nothing, no match
ELSE
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a table called Patient Data that contains Social Security
Numbers. -
the field is called SSN I do not want duplicate SSN's in the
database,
On my input form, I have the following event procedure on the
Before Update
line

Private Sub textSSN_BeforeUpdate(Cancel As Integer)
If SSN = DLookup("SSN", "Patient Data", "[SSN] = '" & textSSN &
"'") Then
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If
End Sub


The message box with "SSN exists. Verify SSN" comes up. So far
so good.
Then when I click ok, I get an Access Popup that states "The value
in the
field or record violates the validation rule for the record or
field ..."

How can I get rid this second popup? This message will confuse the
casual
user. Thanks for your help.


Sandy

 

[Sandy Burgess]

Back again with problems on the new patient input form. Same situation:

Table called Patient Data contains SSN (text field) that can not be
duplicated. I do have a field call ID that is numeric. I have the following
code on the before update (this is the code you wrote for me)

Private Sub textSSN_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String

strCriteria = "[SSN] = '" & Me.textSSN & "' AND " _
& "[ID] <> " & Me.textID

If IsNull(DLookup("SSN", "Patient Data", strCriteria)) Then
'do nothing, no match
Else
MsgBox "SSN exists. Verify SSN."
Cancel = True
Me.textSSN.SelStart = 0
Me.textSSN.SelLength = Len(Me.textSSN)
End If

End Sub


The first time I put a duplicate SSN on the form I get the msg box with "SSN
exists. Verify SSN". When I hit ok. I get an Access Popup that states “The
value in the field or record violates the validation rule for the record or
field ………â€
How can I get rid this second popup? (I'm back where I started on this form
:? )Thanks for your help.

 

[Dale Fye]

Sandy,

Sorry it took me so long to get back to you. Did you resolve this? If not,
I'll be glad to take another look.

Dale

 

[Sandy Burgess]

Thanks to all who answered. I did get this to work.