Detecting Duplicate Entries On Direct Input 
Author Message
 Detecting Duplicate Entries On Direct Input

At work, I have created a database that keeps track of all PC base units.
The data includes workstation names, base serial numbers and asset codes.
All three items are unique to the base unit concerned.
Whilst I have set the relevant fields to indexed (no duplicates), the
duplicate warning only appears once all data has been entered and a new
record is in need of creation.  This leaves one wondereing exactly what
entry has been duplicated.
What I seek is a way to detect a duplicate entry for each individual field
entry using the "after update" event procedure.  I can then use the
IF...THEN statement to act accordingly.
Does anyone know of the code used for such a task please?
When responding to the group, can an e-mail also be sent to me at my works

Firewall will not allow connection to newsgroups.
Regards
Darren
System Support Administrator
IT Services
Motability, UK


Sun, 22 Sep 2002 03:00:00 GMT  
 Detecting Duplicate Entries On Direct Input
Well, if it's a duplicate value, then the value just typed into the control
already exists in that field in the underlying table. So, you could do a
lookup to see.

Why use the form control's AfterUpdate() event? You could use the control's
BeforeUpdate() event instead, and have the ability to cancel the change if
it really is a duplicate.

For this example, let's assume you have a lookup table of operation names,
"lkOperNm", and a field named "fOperNm", and when the user is gaily punching
in new operation names, you wish to prevent duplicates. The code could be
something like this:

    Dim varStoredVal As Variant, varNewVal As Variant, strFldNm As String

    bCanc = False 'Don't forget to change the formal parameter from Cancel
to bCanc.
    strFldNm = "Operation Name" 'Name of field in user-friendly English.
    varNewVal = Trim(Me![fOperNm])
    If (IsNull(varNewVal) Or Len(varNewVal) < 1) Then
        bCanc = True
        MsgBox "You must enter a (unique) value!", vbExclamation,
"DatabaseName"
        Exit Sub
    Else
        varStoredVal = DLookup("[fOperNm]", "lkOperNm", "[fOperNm]=" _
                                 & Chr$(34) & varNewVal & Chr$(34)) _
                                 'Don't use quotes around sought value if
field is numeric!
        If (IsNull(varStoredVal) Or Len(varStoredVal) < 1) Then
            Exit Sub 'OK; not duplicated.
        Else
            bCanc = True
            MsgBox "Sorry; duplicate value in " & Chr$(34) & strFldNm &
Chr$(34) & " field.", _
                          vbExclamation, "DatabaseName"
        End If
    End If

I've written this from memory, so you might have to tweak it a little, but
it should help. Remember that the BeforeUpdate() event has a Cancel
parameter; I change it to bCanc to fit my coding style. And when using
DLookup(), use the quote characters around the search value if looking for a
text value, don't use them if the underlying table field is numeric. I guess
you could use DFirst() instead...

And if you created a standard subprogram for this kind of thing, probably in
the global modules area where it can be called by all forms, with extra
parameters for the actual field name (as opposed to what the user sees on
the form), the table name, and whether the field is numeric or text, then
for each control you could just call a subprogram (from the control's
BeforeUpdate() event) using a SINGLE LINE OF CODE, like this:

    Call mValChkDup(Me![Operation Name], "Operation Name", "fOperNm",
"lkOperNm", True, bCanc)

Where the subprogram's declaration looked like this:

    Sub mValChkDup(varFrmCtrl, ByVal strCtrlNmDsp As String, ByVal strFldNm
As String, _
                                 ByVal strTblNm As String, ByVal bTextField
As Integer, bCanc As Integer)

The Cancel flag (bCanc) gets returned to the calling BeforeUpdate()
subprogram. Saves reinventing the wheel.


Quote:
> At work, I have created a database that keeps track of all PC base units.
> The data includes workstation names, base serial numbers and asset codes.
> All three items are unique to the base unit concerned.
> Whilst I have set the relevant fields to indexed (no duplicates), the
> duplicate warning only appears once all data has been entered and a new
> record is in need of creation.  This leaves one wondereing exactly what
> entry has been duplicated.
> What I seek is a way to detect a duplicate entry for each individual field
> entry using the "after update" event procedure.  I can then use the
> IF...THEN statement to act accordingly.
> Does anyone know of the code used for such a task please?
> When responding to the group, can an e-mail also be sent to me at my works

> Firewall will not allow connection to newsgroups.
> Regards
> Darren
> System Support Administrator
> IT Services
> Motability, UK



Thu, 10 Oct 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. how to detect how long without any input since last input action

2. how to detect how long without any input since last input action

3. Real-time checking of duplicate entries

4. before saving a record, check duplicate entries

5. Deleting duplicate entry in subform

6. Checking For Duplicate Entries using VBA

7. validartion code for duplicate entries

8. Duplicate entries when coping items with VBA macro

9. Duplicate contact Entries

10. Duplicate entries when coping items with VBA macro

11. Duplicate form entry problem with new form

12. Duplicate entries in custom form

 

 
Powered by phpBB® Forum Software