On Tue, 13 Nov 2007 20:01:25 -0800, Wayne wrote:
I'm using the following code to check for duplicate names when I exit
the field "MemberName" on a form:
If DCount("*", "tblMembers", "[MemberName] = '" & Me![MemberName] &
"'") <0 Then
DoCmd.CancelEvent
MsgBox "This Name is already in use." , vbExclamation, "Error"
Me![MemberName].SetFocus
End If
This works fine unless the MemberName in question contains an
apostrophe eg. O'Donnell Jim.
In this case the code is seeing the name as a duplicate. Is there a
simple way to fix this?
If DCount("*", "tblMembers", "[MemberName] = """ & Me![MemberName] &
"""") <0 Then
Note: If you use the [MemberName] control's BeforeUpdate event, you
can use
Cancel = True
to cancel the entry being saved, and focus is automatically returned
to the MemberName control.
If DCount("*", "tblMembers", "[MemberName] = """ & Me![MemberName] &
"""") <0 Then
MsgBox "This Name is already in use." , vbExclamation, "Error"
Cancel = True
But what happens if there are several different Smith's who wish to
become members? Does one of them get renamed 'Jones' and the other
'Anderson'? <gI think, if you wish to assure that the same person is
not entered more than once, you need to allow the user to enter the
same name (because it's a different person).
If DCount("*", "tblMembers", "[MemberName] = """ & Me![MemberName] &
"""") <0 Then
If MsgBox("This Name is already in use." & vbNewLine & "Is this
a different person than already entered?" , vbYesNo + vbExclamation,
"Error") = vbNo Then
Cancel = True
End If
End If
If No is selected the name is not saved, otherwise it is saved.
Use the MemberID (or some other unique field) to differentiate between
members, not their name.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail