on entering a number in a textbox, check if entry already exists?

[kate10123]

Hi there,

An administrator adds students to a database via a form. He types in the student number and the other student details and then this is saved in the database. The problem is, sometimes he does not check if the student already exists and it doesn't return the duplication error until he tries to save the record manually.

Is there a way of putting a check on exiting one of the textboxes to save this user getting to the end of the data entry to find it exists already?

 

[missinglinq]

Two questions:

1. Is the student number defined as Text or Number in the underlying table?
   
2. What exactly do you want to happen if a duplicate is detected? Do you want to dump the record, or give the user the opportunity to re-enter the student number, in case they've merely entered it incorrectly?

Generally speaking, you'll probably need to use the AfterUpdate event of the student number textbox to check duplicates.

 

[dkinley]

Hi ... one method is to do something like the following on the BeforeUpdate event of Student Number field ...

    Dim iStudentID As Long 'the primary key of the student table
 
    iStudentID = Nz(DLookup("[iStudentID ]", "tblStudent", _
        "[StudentNumber]= " & Me.txtControlNameForStudentNumber), 0)
    If iStudentID <> 0 Then
        MsgBox "This student number already exists." & vbCrLf & _
            "Please enter a different student number.", _
            vbCritical, "Duplicate Student Number"
        Me.txtControlNameForStudentNumber.Undo
        Cancel = True
    End If

You will need to do the appropriate substitution for the table/field names. Hope that helps!

-dK

 

[missinglinq]

Note that dk's excellent code is assuming that the student number is an actual number! If the student number is defined as Text, as it really should be, the DLookup() syntax will be slightly different.

As a rules, fields that only contain digits should be defined as Text if they're used for identification purposes (i.e. SSNs, telephone numers, account numbers) and defined as Numeric if they are actually going to be used for mathematical purposes.

 

[dkinley]

Why thank you!

Kate, in response to Linq's post - he brings up a very, very, very good point.

What do you want to do with once it's a duplicate? Cancel the form? Call the attention to the correct record? You might be writing a whole buncha code when a simpler solution might present itself once this question is answered.

For instance, perhaps fronting your form with the student number being a look-up control (like a find). Then if the number is there, the form is populated and they instructor can guarentee it by the student name. This will provide a double-check of the correct name-to-number entry in the beginning. Then, if no number exists, you could use the NotInList event to add a student number/new record.

Now, this might be a "6 of one half a dozen of the other" scenario in the code writing - but the benefit of the approach might be yielded in data integrity, etc.

-dK

 

[Dennisk]

another problem you will encounter is when an existing key is changed so it is a duplicate of another key.

So you need to enhance your code to cater for new records.
then when checking duplicates for existing records you will need to exclude the current record.


here is an example checking for a duplicate patient referral date

 If Me.NewRecord Then
            strSQL = "[ClientID]=" & Me![ClientID] & " AND [ReferralDate] =#" & Format(Me![ReferralDate], "mm/dd/yy") & "# AND Deleted=0"
            If DCount("[ReferralID]", "tblReferral", strSQL) > 0 Then
                MsgBox "This Referral Date already exists for this Client", vbCritical
                Cancel = True
                Exit Sub
            End If
        Else
            strSQL = "[ClientID]=" & Me![ClientID] & " AND [ReferralID] <>" & Me![ReferralID] & " AND [ReferralDate] =#" & Format(Me![ReferralDate], "mm/dd/yy") & "# AND Deleted=0"
            If DCount("[ReferralID]", "tblReferral", strSQL) > 0 Then
                MsgBox "This Referral Date already exists for this Client", vbCritical
                Cancel = True
                Exit Sub
            End If
        End If

 

[kate10123]

Hi All,

Sorry for the delay in replying. The StudentID was defined as a number in the underlying table and the code kindly provided by Denniskworked well!

 Dim iStudentID As Long 'the primary key of the student table

    iStudentID = Nz(DLookup("[iStudentID ]", "tblStudent", _
        "[StudentNumber]= " & Me.txtControlNameForStudentNumber), 0)
    If iStudentID <> 0 Then
        MsgBox "This student number already exists." & vbCrLf & _
            "Please enter a different student number.", _
            vbCritical, "Duplicate Student Number"
        Me.txtControlNameForStudentNumber.Undo
        Cancel = True
    End If

Thanks to all those that provided a solution!

 

[tomsor]

This also helped me, but how do I open (or go to) the correct "duplicate" record after the user has clicked "OK" on the Messagebox?

I have searched all over for a easy solution for this, but can't find any..

My code looks like this:

Dim icount As Long
icount = Nz(DLookup("WorkerID", "Personalia", "WorkerID=" & Me.WorkerIDFIELD), 0)
If icount <> 0 Then
Beep
MsgBox "Worker ID allready exists. You must choose an uniqe ID number!"
Cancel = True
Undo
End If

WorkerID is a number field in the Personalia Table. WorkerIDFIELD is the formfield where the user enters the data in the form.
I want to go to the existing record instead of just running the UNDO command.