DLookup duplicate question

[Sonia]

Hi,
I am trying to enter information on a form (in this case a name). I have a
txt box that if I enter the name should first check for duplicates and if
there is a duplicate, alert me, but if not a duplicate allow me to continue.

This is the code I use:

Private Sub Client_Name_BeforeUpdate (Cancel As Integer)

If (Not IsNull (DLookup("[Client_Name]", "Client", "[Client_Name]='" &
Me!Client_Name & "'"))) Then MsgBox "This client already exists."
Cancel = True
Me!Client_Name.Undo

End Sub

The problem is that I'm working with full names and I wanted that the txt
box only appeared if the full name is a duplicate and not only a part of the
name. How can I do that?

Thanks.

 

[strive4peace]

Hi Sonia,

If you do not have an autonumber ClientID, it would be good to add it to
the table and the form ...

'~~~~~~~~~~~~~~~~~`
dim mClientID as long
if me.newRecord then
mClientID = 0
else
mClientID = me.ClientID
end if

mClientID = nz(DLookup("[ClientID]", "Client", "[Client_Name]='" &
nz(Me.Client_Name) & "' AND ClientID <> " & mClientID), 0)

If mClientID > 0 then
MsgBox "This client already exists." _
,,"ClientID: " & mClientID
Cancel = True
Me.Client_Name.Undo
end if
'~~~~~~~~~~~~~~~~~`

I like to use . referencing for control on the form you are behind
instead of ! so I changed your code there...also, you want it to be okay
to change a client name and then change it back to the way it was (on
the same record) without getting an error...so allow it if the ID is the
same

space underscore at the end of a line means that statement is continued
on the next line

you can also set a UNIQUE index in your table on client name so that, if
someone happens to enter a record to the table not using the form, a
duplicate name will not be allowed by design.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Sonia]

That worked great Crystal, thank you so much for your help!

Regards
Sonia

"strive4peace" escreveu:

Hi Sonia,

If you do not have an autonumber ClientID, it would be good to add it to
the table and the form ...

'~~~~~~~~~~~~~~~~~`
dim mClientID as long
if me.newRecord then
mClientID = 0
else
mClientID = me.ClientID
end if

mClientID = nz(DLookup("[ClientID]", "Client", "[Client_Name]='" &
nz(Me.Client_Name) & "' AND ClientID <> " & mClientID), 0)

If mClientID > 0 then
MsgBox "This client already exists." _
,,"ClientID: " & mClientID
Cancel = True
Me.Client_Name.Undo
end if
'~~~~~~~~~~~~~~~~~`

I like to use . referencing for control on the form you are behind
instead of ! so I changed your code there...also, you want it to be okay
to change a client name and then change it back to the way it was (on
the same record) without getting an error...so allow it if the ID is the
same

space underscore at the end of a line means that statement is continued
on the next line

you can also set a UNIQUE index in your table on client name so that, if
someone happens to enter a record to the table not using the form, a
duplicate name will not be allowed by design.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi,
I am trying to enter information on a form (in this case a name). I have a
txt box that if I enter the name should first check for duplicates and if
there is a duplicate, alert me, but if not a duplicate allow me to continue.

This is the code I use:

Private Sub Client_Name_BeforeUpdate (Cancel As Integer)

If (Not IsNull (DLookup("[Client_Name]", "Client", "[Client_Name]='" &
Me!Client_Name & "'"))) Then MsgBox "This client already exists."
Cancel = True
Me!Client_Name.Undo

End Sub

The problem is that I'm working with full names and I wanted that the txt
box only appeared if the full name is a duplicate and not only a part of the
name. How can I do that?

Thanks.

 

[strive4peace]

you're welcome, Sonia happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

That worked great Crystal, thank you so much for your help!

Regards
Sonia

"strive4peace" escreveu:

Hi Sonia,

If you do not have an autonumber ClientID, it would be good to add it to
the table and the form ...

'~~~~~~~~~~~~~~~~~`
dim mClientID as long
if me.newRecord then
mClientID = 0
else
mClientID = me.ClientID
end if

mClientID = nz(DLookup("[ClientID]", "Client", "[Client_Name]='" &
nz(Me.Client_Name) & "' AND ClientID <> " & mClientID), 0)

If mClientID > 0 then
MsgBox "This client already exists." _
,,"ClientID: " & mClientID
Cancel = True
Me.Client_Name.Undo
end if
'~~~~~~~~~~~~~~~~~`

I like to use . referencing for control on the form you are behind
instead of ! so I changed your code there...also, you want it to be okay
to change a client name and then change it back to the way it was (on
the same record) without getting an error...so allow it if the ID is the
same

space underscore at the end of a line means that statement is continued
on the next line

you can also set a UNIQUE index in your table on client name so that, if
someone happens to enter a record to the table not using the form, a
duplicate name will not be allowed by design.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi,
I am trying to enter information on a form (in this case a name). I have a
txt box that if I enter the name should first check for duplicates and if
there is a duplicate, alert me, but if not a duplicate allow me to continue.

This is the code I use:

Private Sub Client_Name_BeforeUpdate (Cancel As Integer)

If (Not IsNull (DLookup("[Client_Name]", "Client", "[Client_Name]='" &
Me!Client_Name & "'"))) Then MsgBox "This client already exists."
Cancel = True
Me!Client_Name.Undo

End Sub

The problem is that I'm working with full names and I wanted that the txt
box only appeared if the full name is a duplicate and not only a part of the
name. How can I do that?

Thanks.