Help with Finding and erroring on Duplicate Values

[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

 

[Marshall Barton]

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
---------------------------------------------

The only reason that comes to mind is if then text box
string contains an apostophe. Could you provide some
examples that don't work along with an explanation of what
does happen?

Note that setting Cancel = True precludes the use of
CancelEvent. Get rid of the CancelEvent line.

Also note that DoCmd.Save saves the form's design, which is
a bad thing to do in a runnung application. Your probably
intended to save the new record, If so, use this instead:
Me.Dirty = False

 

[Kath via AccessMonster.com]

Thanks for your help. Ok, well just for testing purposes I entered A1234 and
when I move the focus away from the 2nd record instead of running the
duplication check I get a Run-time error 13 Type mismatch. The field is a
text field so I should be able to have a combination of both characters and
numbers?

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

[quoted text clipped - 15 lines]

End If
---------------------------------------------

The only reason that comes to mind is if then text box
string contains an apostophe. Could you provide some
examples that don't work along with an explanation of what
does happen?

Note that setting Cancel = True precludes the use of
CancelEvent. Get rid of the CancelEvent line.

Also note that DoCmd.Save saves the form's design, which is
a bad thing to do in a runnung application. Your probably
intended to save the new record, If so, use this instead:
Me.Dirty = False

 

[Marshall Barton]

What code are you using now? Please post a Copy/Paste of
the code so we're not trying to debug your typing.
--
Marsh
MVP [MS Access]

Thanks for your help. Ok, well just for testing purposes I entered A1234 and
when I move the focus away from the 2nd record instead of running the
duplication check I get a Run-time error 13 Type mismatch. The field is a
text field so I should be able to have a combination of both characters and
numbers?

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

[quoted text clipped - 15 lines]

End If
---------------------------------------------

The only reason that comes to mind is if then text box
string contains an apostophe. Could you provide some
examples that don't work along with an explanation of what
does happen?

Note that setting Cancel = True precludes the use of
CancelEvent. Get rid of the CancelEvent line.

Also note that DoCmd.Save saves the form's design, which is
a bad thing to do in a runnung application. Your probably
intended to save the new record, If so, use this instead:
Me.Dirty = False

 

[Kath via AccessMonster.com]

The only code that I have is on the before update as previously stated. I am
not an "expert" coder thus keep things pretty basic.

What code are you using now? Please post a Copy/Paste of
the code so we're not trying to debug your typing.

Thanks for your help. Ok, well just for testing purposes I entered A1234 and
when I move the focus away from the 2nd record instead of running the

[quoted text clipped - 20 lines]