Finding Duplicates Using Loops

  • Thread starter Thread starter DenBis
  • Start date Start date
D

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
 
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
 
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
 
John,
Thank you for your solution. This is the type of thing I
was looking for.
Cheers
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
 
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
 
Roger,
Your advice certainly makes sense and I paln to do exactly
what you recommend - including both solutions.
Thanks again
DenBis
 
Back
Top