Checking for duplicates

G

gb

Hello

I am looking to replicate work I currently undertake in Excel by using an
Access database. At this stage, I want to verify whether this is going to
possible, and if so, how.

As part of the work, I need to enter a number, or number range, and if the
number, or a number within the range, already exists within the database, to
flag up a warning by means of a msgbox.

The numbers to be inputted are all 11 digits in total. However, they are
entered in 3 parts. The first part is the first 7 digits. The second and
third parts are the last 4 digits. They need to be stored as text, as any
of the numbers could start with 0. If the number being entered is a single
number, parts 2 and 3 are identical. Part 3 is different to part 2 if
entering a range. For example:

Single number: 0123456 7890 7890
Range number: 0123456 7895 7899

So if I were to enter 0123456 7897 7897, this should flag up an error (as it
falls within the 0123456 7895 to 7899 range). If I were to enter 0123456
7891 to 7905, this should also flag up an error, as a number range that
falls within this new range already exists.

I hope this makes sense. The check needs to work whether a single or range
number is being entered.

Can someone tell me how in VBA I achieve this, please?

Would it also be possible for the msgbox alert to also advise which record
number is causing the problem? (assuming a standard autonumber for each
record).

Thanks for your help with this. I'm grateful for the efforts everyone puts
in here.

gb
 
D

Douglas J. Steele

Sometimes when trying to solve a problem, it's easier to turn it around. In
my mind, this is one of those cases.

Rather than trying to figure out the logic to determine whether the new
entry conflicts with existing ones, it's easier to determine when it doesn't
conflict.

For instance, if the highest number in the new input is less than the lowest
number of an existing entry, you know there's no conflict. Similarly, if the
lowest number in the new input is greater than the highest number of an
existing entry, there's no conflict. As long as one of these conditions is
true, there's no conflict. Needing only one of a bunch of conditions to be
true implies using Or, as you'll see below.

Since you don't give me names with which to work, I'll make them up. I'll
assume you've got table MyTable, with fields Part1, Part2 and Part3, and
that the 3 fields are text. I'll further assume that your new number is
going to be stored in 3 variables strValue1, strValue2 and strValue3.

Therefore, for a given row in the table, the following will be true if
there's no conflict:

(Part1 = Value1) AND
((Part2 > Value3 OR (Part3 < Value2))

In order to only find the the conflicts, you can use something like the
following untested air code:

Function DataConflicts( _
Value1 As String, Value2 As String, Value3 As String _
) As Boolean

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Part1, Part2, Part3 " & _
"FROM MyTable "
"WHERE Part1 = '" & strValue1 & "' AND " & _
"(((Part2 > '" & strValue3 & "') OR " & _
"(Part3 < '" & strValue2 & "')) = False) "

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)

DataConflicts = (rsCurr.BOF And rsCurr.EOF)

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

End Function

You can pass the values in strValue1, strValue2 and strValue3 to this
function, and it will return True if there's no conflict, or False if there
is.

Note that I'm using DAO in the code above. If you're using Access 2000 or
2002, you'll need to ensure that a reference has been set to DAO, as by
default, those versions of Access only have a reference to ADO.
 
G

gb

Douglas

Thank you for taking the time to put this together.

This is more complex than I imagined it to be. I'm another of those people
that don't understand VBA that well. I have no idea what I need to do in
order to pass values into strings or how to call functions, or indeed, where
I store this function.

I'll leave the choice to you whether you explain these specifics to me,
although, if you do, I'd be very grateful to you.

In the meantime, I continue to read my VBA for Dummies book :)

Thanks again

gb

Sometimes when trying to solve a problem, it's easier to turn it around. In
my mind, this is one of those cases.

Rather than trying to figure out the logic to determine whether the new
entry conflicts with existing ones, it's easier to determine when it doesn't
conflict.

For instance, if the highest number in the new input is less than the lowest
number of an existing entry, you know there's no conflict. Similarly, if the
lowest number in the new input is greater than the highest number of an
existing entry, there's no conflict. As long as one of these conditions is
true, there's no conflict. Needing only one of a bunch of conditions to be
true implies using Or, as you'll see below.

Since you don't give me names with which to work, I'll make them up. I'll
assume you've got table MyTable, with fields Part1, Part2 and Part3, and
that the 3 fields are text. I'll further assume that your new number is
going to be stored in 3 variables strValue1, strValue2 and strValue3.

Therefore, for a given row in the table, the following will be true if
there's no conflict:

(Part1 = Value1) AND
((Part2 > Value3 OR (Part3 < Value2))

In order to only find the the conflicts, you can use something like the
following untested air code:

Function DataConflicts( _
Value1 As String, Value2 As String, Value3 As String _
) As Boolean

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Part1, Part2, Part3 " & _
"FROM MyTable "
"WHERE Part1 = '" & strValue1 & "' AND " & _
"(((Part2 > '" & strValue3 & "') OR " & _
"(Part3 < '" & strValue2 & "')) = False) "

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)

DataConflicts = (rsCurr.BOF And rsCurr.EOF)

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

End Function

You can pass the values in strValue1, strValue2 and strValue3 to this
function, and it will return True if there's no conflict, or False if there
is.

Note that I'm using DAO in the code above. If you're using Access 2000 or
2002, you'll need to ensure that a reference has been set to DAO, as by
default, those versions of Access only have a reference to ADO.
 
J

Jamie Collins

gb said:
This is more complex than I imagined it to be. I'm another of those people
that don't understand VBA that well.
Click to expand...

Instead of using VBA, you could create a 'stored query' e.g.
(untested)

CREATE PROCEDURE HasDataConflicts
(
Arg1 TEXT,
Arg2 TEXT
)
AS
SELECT COUNT(*) = 0
FROM MyTable
WHERE Part1 = Arg1
AND NOT
(
Part2 > Arg2
OR Part3 < Arg2
)
;

....except you can only do it *this* way using code. I assume there
must be a way of doing the same using the MS Access UI. Anyone?

Jamie.

--
 
J

Jamie Collins

[email protected] (Jamie Collins) wrote ...
Instead of using VBA, you could create a 'stored query' e.g.
(untested)

CREATE PROCEDURE HasDataConflicts
(
Arg1 TEXT,
Arg2 TEXT
)
AS
SELECT COUNT(*) = 0
FROM MyTable
WHERE Part1 = Arg1
AND NOT
(
Part2 > Arg2
OR Part3 < Arg2
)
;

...except you can only do it *this* way using code. I assume there
must be a way of doing the same using the MS Access UI. Anyone?
Click to expand...

I finally got round to installing MS Access and looking at the sql
that had been created server side. Well, after I'd edited it to make
it human readable (e.g. got rid of the superfluous brackets and
parens), restored the WHERE condition using the NOT keyword, etc), it
looked like this:

PARAMETERS Arg1 Text ( 255 ), Arg2 Text ( 255 );
SELECT COUNT(*)=0
FROM MyTable
WHERE Part1=Arg1 And Not (Part2>Arg2 Or Part3<Arg2);

Jamie.

--
 
G

gb

Thank you for helping Jamie. It's kind of you.

I'm still not sure how I would pass the values over and how, once entering
the number, it would automatically check to see if the number is duplicated
or not.

:-((

If you want to have an email exchange to work this out together, that would
be good.

Thanks again ;-)

gb
 
J

Jamie Collins

gb said:
Thank you for helping Jamie. It's kind of you.

I'm still not sure how I would pass the values over and how, once entering
the number, it would automatically check to see if the number is duplicated
or not.
Click to expand...

With respect, perhaps you need to take a step backwards and learn the
fundamentals. For example, find out how to retrieve some known values
from your database.

When you are ready, this thread will be waiting for you in the usenet
archive <g>.

Jamie.

--
 
Top