Help with Finding and erroring on Duplicate Values

  • Thread starter Kat via AccessMonster.com
  • Start date
K

Kat via AccessMonster.com

Hello,
I have a db that I am working on. On the "Before Update" of a txtChartID I
have the following code which works great if I use all numbers. If I use
numbers and characters it does not work at all - the user cannot move from
the field if it is a duplicate value ? Any ideas on what I may need to
change?

---------------------------------------------
If DLookup("CA_1_2_ClIENTID", "tblCA_1_2_Client_Data", "CA_1_2_CLIENTID = '"
& Me!txtCA_1_2_CLIENTID & "'") Then

MsgBox "You have entered a duplicate Client / Chart No. Please enter a new
patient.", vbCritical, "Duplicate Patient Error"
Cancel = True
Me.Undo
DoCmd.CancelEvent
Else
DoCmd.Save
End If
 
R

RuralGuy

Hello,
I have a db that I am working on. On the "Before Update" of a txtChartID I
have the following code which works great if I use all numbers. If I use
numbers and characters it does not work at all - the user cannot move from
the field if it is a duplicate value ? Any ideas on what I may need to
change?

---------------------------------------------
If DLookup("CA_1_2_ClIENTID", "tblCA_1_2_Client_Data", "CA_1_2_CLIENTID = '"
& Me!txtCA_1_2_CLIENTID & "'") Then

MsgBox "You have entered a duplicate Client / Chart No. Please enter a new
patient.", vbCritical, "Duplicate Patient Error"
Cancel = True
Me.Undo
DoCmd.CancelEvent
Else
DoCmd.Save
End If
---------------------------------------------
Click to expand...

Try:
If Not IsNull(DLookup("CA_1_2_ClIENTID", "tblCA_1_2_Client_Data",
"CA_1_2_CLIENTID = '"
& Me!txtCA_1_2_CLIENTID & "'")) Then
MsgBox "You have entered a duplicate Client / Chart No. Please enter a new
patient.", vbCritical, "Duplicate Patient Error"
Cancel = True
Me.Undo
End If
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
Top