Duplicate search

[J.C.]

This is probably very simple, but the answer eludes me:

In a data entry form, I need to programatically make sure
that one of the fields is not duplicated. There is
probably a way to use VBA to search all entries in a
specific column of the same table being added to.

Specifically, I have an event procedure that needs to
check all rows of the table before adding data to the
column in the new row.

Is this clear?

jc

 

[Dan Artuso]

Hi,
It sounds like you could use the DLookup function or even
DCount.
Look them up in Help and see if it's what you need.
They will both lookup values in a field in a table.
You could use DCount to count the occurences of whatever
you're searching for, if it retunrs 0, you know it's not in the table
already.

 

[HSalim]

JC

Just create a unique index on the column and the database will take care of
the checking for you.

I realize you might want to provide a more user friendly error message.
However, that indicates that
you are relying on the user to provide a unique value - which can be a bad
idea.
Instead, you should be providing that value programatically.

HS

 

[J.C.]

Dan -

I got a couple of responses, (thanks!), but I'm having a
problem making it work. I'm probably missing something
simple - but a couple of hours of frustration lead to this
message.

Here's an abbreviation of the code I created:

Dim strResult As String
Dim nCounter2 As Integer

nCounter2 = DCount _
("[CF_Code]", "Company/Firm", "[CF_Code] = _
+strResult")

CF_Code is the table column I'm checking

Company/Firm is the table name

strResult holds the string that I need to see if already
exists in any CF_Code field in the table.

The syntax is apparently wrong, but I can't figure out
why. The debugger halts at the DCount line. Do you see
the problem?

jc

 

[Dan Artuso]

Hi,
It's always tricky the first time you try these things.
What you're doing is comparing CF_Code to the string literal
+strResult.
You have to let Access evaluate the value of strResult and then concatenate
it into your string. An added complexity is that if your criteria is a string,
you have to delimit it with single quotes. If it's numerical, you don't delimit with anything
and if it's a date, you delimit with #.

So in your case:
nCounter2 = DCount _
("[CF_Code]", "Company/Firm", "[CF_Code] = '" & _
strResult & "')"

unless CF_Code IS a numerical value:
nCounter2 = DCount _
("[CF_Code]", "Company/Firm", "[CF_Code] = " & _
strResult & ")"

--
HTH
Dan Artuso, Access MVP

Dan -

I got a couple of responses, (thanks!), but I'm having a
problem making it work. I'm probably missing something
simple - but a couple of hours of frustration lead to this
message.

Here's an abbreviation of the code I created:

Dim strResult As String
Dim nCounter2 As Integer

nCounter2 = DCount _
("[CF_Code]", "Company/Firm", "[CF_Code] = _
+strResult")

CF_Code is the table column I'm checking

Company/Firm is the table name

strResult holds the string that I need to see if already
exists in any CF_Code field in the table.

The syntax is apparently wrong, but I can't figure out
why. The debugger halts at the DCount line. Do you see
the problem?

jc

-----Original Message-----
Hi,
It sounds like you could use the DLookup function or even
DCount.
Look them up in Help and see if it's what you need.
They will both lookup values in a field in a table.
You could use DCount to count the occurences of whatever
you're searching for, if it retunrs 0, you know it's not in the table
already.

--
HTH
Dan Artuso, Access MVP





.

 

[J.C.]

Dan -
Sorry for the slow respone - got called out of town.

I got it working, but there were a couple of problems with
your example:
nCounter2 = DCount _
("[CF_Code]", "Company/Firm", "[CF_Code] = '" & _
strResult & "')"
In the criteria section, I needed to add a ( between the "
and the [, and then add a ) at the end to close the
function.

It was the need to enclose the variable strResult in
single quotes that threw me. C++ was never like this

Thanks for your help!!!

jc

-----Original Message-----
Hi,
It's always tricky the first time you try these things.
What you're doing is comparing CF_Code to the string literal
+strResult.
You have to let Access evaluate the value of strResult and then concatenate
it into your string. An added complexity is that if your criteria is a string,
you have to delimit it with single quotes. If it's

numerical, you don't delimit with anything

and if it's a date, you delimit with #.

So in your case:
nCounter2 = DCount _
("[CF_Code]", "Company/Firm", "[CF_Code] = '" & _
strResult & "')"

unless CF_Code IS a numerical value:
nCounter2 = DCount _
("[CF_Code]", "Company/Firm", "[CF_Code] = " & _
strResult & ")"

--
HTH
Dan Artuso, Access MVP

Dan -

I got a couple of responses, (thanks!), but I'm having a
problem making it work. I'm probably missing something
simple - but a couple of hours of frustration lead to this
message.

Here's an abbreviation of the code I created:

Dim strResult As String
Dim nCounter2 As Integer

nCounter2 = DCount _
("[CF_Code]", "Company/Firm", "[CF_Code] = _
+strResult")

CF_Code is the table column I'm checking

Company/Firm is the table name

strResult holds the string that I need to see if already
exists in any CF_Code field in the table.

The syntax is apparently wrong, but I can't figure out
why. The debugger halts at the DCount line. Do you see
the problem?

jc

-----Original Message-----
Hi,
It sounds like you could use the DLookup function or even
DCount.
Look them up in Help and see if it's what you need.
They will both lookup values in a field in a table.
You could use DCount to count the occurences of whatever
you're searching for, if it retunrs 0, you know it's

not
in the table

already.

--
HTH
Dan Artuso, Access MVP


This is probably very simple, but the answer eludes me:

In a data entry form, I need to programatically make sure
that one of the fields is not duplicated. There is
probably a way to use VBA to search all entries in a
specific column of the same table being added to.

Specifically, I have an event procedure that needs to
check all rows of the table before adding data to the
column in the new row.

Is this clear?

jc


.

.