Finding Duplicates Using Loops

[DenBis]

Good day,

Components:
Form: name - frmQuals
Table - name - tblQuals (source to frmQuals)
Textbox - name: Qual

This is what I want to accomplish:

Form frmQuals will be used to enter Qualifications via the
field/textbox "Qual". Presumably in the 'On Exit" event, I
want to check the underlying table (tblQuals) to see if
the Qualification already exists. If it doesn't exist,
fine get out of the sub. If it does exist, then I will
display a MsgBox saying it's a duplicate and proceed to
delete the new entry.

I tried finding information using Loop. until (which I am
not familiar with) and even tried to code an SQL statement
(based on the "Find Duplicates Query" wizard) though I
don't know how the code actually 'acknowledges' that there
ARE duplicates.

What is the best way of accomplishing this?

Thank you

DenBis

 

[John Nurick]

Hi DenBis,

Do this in the BeforeUpdate event of the textbox.

The simplest way is with the DCount() function, something like this:

If DCount("Qual", "tblQuals", _
"Qual='" & Me.ActiveControl.Value & "'") > 0 Then
Cancel = True
MsgBox "This qualification already exists", _
vbOkOnly + VBExclamation
End If

 

[Roger Carlson]

Perhaps a better method is to set a Unique Index on the Qual field right at
the table level. With that in place, you cannot even save a record that has
a duplicate value. Granted, this gives you a nasty, system error message,
but you can trap for this error in the OnError event of the form and replace
the system message with a more meaningful one of your own. Best of all,
since the duplicate-checking is at the database level, it cannot be
circumvented in any way.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[DenBis]

John,
Thank you for your solution. This is the type of thing I
was looking for.
Cheers
DenBis

 

[DenBis]

Good day Roger,
Oddly enough, your solution came to mind shortly after I
sent off my post! However, as you stated, the system
message needs a lot to be desired.
Thanks for your time!
DenBis

 

[Roger Carlson]

Actually, for a truly robust system, you should use both methods. John's
BeforeUpdate method gives you greater control in the form over finding
duplicates than waiting for the record-save to identify the unique index
violation. However, if you use only that method, your field can get
duplicate values through other means: i.e. direct manipulation of the
table, update queries, other forms, etc. Relying ONLY on code to keep
duplicates out is dangerous. Therefore, you should ALSO create a unique
index. Because it is a rule at the database engine level, it will keep you
from EVER having a duplicate in the field.


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[DenBis]

Roger,
Your advice certainly makes sense and I paln to do exactly
what you recommend - including both solutions.
Thanks again
DenBis