Help Needed With DCount

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?

Re: Help Needed With DCount

On Tue, 13 Nov 2007 20:01:25 -0800, Wayne
wrote:

Yes. Use the Replace function to double up on the single quotes.
-Tom.



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

Re: Help Needed With DCount

On Nov 14, 2:08 pm, Tom van Stiphout wrote:
> On Tue, 13 Nov 2007 20:01:25 -0800, Wayne
> wrote:
>
> Yes. Use the Replace function to double up on the single quotes.

Thanks for the response Tom. I changed the single quotes to doubles,
but the code no longer picks up duplicate MemberNames.

Re: Help Needed With DCount

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'? I 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

Re: Help Needed With DCount

Wayne wrote in news:1195012885.569056.40280
@v29g2000prd.googlegroups.com:

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

I would try "[MemberName] = " & Chr$(34) & Me![MemberName] & Chr$(34)

--
lyle fairfield

Re: Help Needed With DCount

> I would try "[MemberName] = " & Chr$(34) & Me![MemberName] & Chr$(34)
>
> --
> lyle fairfield

Cheers Lyle. Works perfectly.