Solved

Check for duplicate field before saving

Posted on 2015-01-26
15
191 Views
Last Modified: 2015-01-28
I have a form for creating new student accounts. We have to create a username for the student and while our preference is first initial  + last name, this cannot always work because of people with the similar names (e.g., Mary Smith and Michelle Smith). The field is a text field with the index (no duplicates) property so we find out once the record is complete if we've duplicated someone.

Is there a way to check this field before all the data is entered so we can create a new username right away? Is there some code that I can add to the field in the OnChange event that will see if this username is already present in the table and pop up with a message?
0
Comment
Question by:talekzander
  • 8
  • 7
15 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40571600
Do NOT use the Change event.  The Change event fires once for EACH character typed so it is useless for this purpose.

Most field level validation (except checking for empty) is done in the control's BeforeUpdate event.  You check for a duplicate and then cancel the event if one is found giving the user a chance to try again.  The code below always checks for a duplicate in the case of a new record but only checks for existing records if the current value of ID is not equal to the old value (what is currently stored in the table).  The reason for adding this check is because occasionally, the user will dirty the field by accident and not really mean to change it so you don't want to find a duplicate that isn't really there.
If Me.NewRecord = True OR (Me.NewRecord = False AND Me.yourID & "" <> Me.YourID.OldValue & "") Then
    If Dcount("YourID", "YourTable", "YourID = '" & Me.YourID & "'") > 0 Then
        Msgbox "This ID is a duplicate.  Please try again.",vbOKOnly
        Cancel = True
        Exit Sub
    End If
End if

Open in new window

Then in the BeforeUpdate event of the FORM, you need to check to make sure that the field actually has a value.
If Me.YourID & "" = "" Then
    Msgbox "YourID is required.",vbOKOnly
    Me.YourID.SetFocus
    Cancel = True
    Exit Sub
End If

Open in new window

You should also mark the field as required and add a unique index for it in your table definition.  That will protect the validity of the data should someone try to update it from some other place than this form.  The only reason you would put the validation code in the form since it technically isn't necessary once you establish the declarative RI as it should be, is because you want to improve the user experience and give him a more friendly error message.
0
 

Author Comment

by:talekzander
ID: 40573073
Thank you for the proposed solution. I'm getting an error when I try to test the form. It is generic error "The expression Before Update you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control."

I'm pretty certain that I've done something wrong despite your instructions.

Here's what I did:

I made sure that the field in the table was required and indexed with Yes (No Duplicates)
On the form, I added the code from your first block to the BeforeUpdate event of the control for [username].
I changed all the "YourID" references in your code to "username" and the reference to "YourTable" to "Accounts".

On the form, I added the code from your second block to the BeforeUpdate event of the form. Again, I changed all the "YourID" references in your code to "username".

So the code now looks like:
If Me.NewRecord = True Or (Me.NewRecord = False And Me.username & "" <> Me.username.OldValue & "") Then
    If DCount("username", "Accounts", "Username = '" & Me.username & "'") > 0 Then
        MsgBox "This username is a duplicate.  Please try again.", vbOKOnly
        Cancel = True
        Exit Sub
    End If
End If

If Me.username & "" = "" Then
    MsgBox "Username is required.", vbOKOnly
    Me.username.SetFocus
    Cancel = True
    Exit Sub
End If

Did I put it in the wrong place? Incorrect punctuation? Thank you in advance for your help.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40574037
Compact the database
Does the code compile?
Which line throws the error?

If you create a break at the first line of each procedure, you can step through the code.  That may help identify the problem.
0
 

Author Comment

by:talekzander
ID: 40574071
I compacted the database.

I get no errors when I choose Debug, Compile.

I tried adding a breakpoint to each procedure, but got the same error. I don't get the kind of error that will let me choose debug. I've attached a screen shot of the error.
ErrorMessage.jpg
vbacode.jpg
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40574346
Add:

Option Explicit

as the second line of the module.  This tells Access to require field definitions so you may get a compile error.

ALWAYS set the option to require variable declaration to true.  You always want to find typos in variable names up front.  You don't want Access creating "duplicate" variables because of a typo.

Click in the grey margin to the left of each "if" statement.  That will leave a "dot" there marking the stop point.  Then you can step through the code.
0
 

Author Comment

by:talekzander
ID: 40575494
Thank you. I added option explicit as you instructed and I changed the options in visual basic to Require Variable Declaration.

Now I'm unable to compile
ErrorMessage.jpg
And I get the same error in my form.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40575534
No.  The error is different.  You now have a compile error which is better than a run time error.  

The error is probably referring to the "option Explicit".  I said to place it as the second line of the Module, you placed it inside each procedure.  So, delete them from where it is and move one of them to just below the Option Compare.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:talekzander
ID: 40575559
Ahh! Moved the "Option Explicit". Now it will compile. But I get the same error when I try to use the form. Just the generic one I included in the image above "errormessage.jpg".
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40575597
So, the code doesn't stop in the procedure or did you remove the stops also?
0
 

Author Comment

by:talekzander
ID: 40575605
I left the stops in. The code didn't stop.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40575842
Can you post the database stripped down to the form with the problem (and whatever it needs to support it) if necessary.
0
 

Author Comment

by:talekzander
ID: 40576251
Yes. Plesae see attached. I left some data in. The form that is in question is named Accounts. It is based on a query qryAccounts.
elearning---Copy.accdb
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40576423
It works perfectly for me.  It won't let me save a record with an empty user name and it won't let me save one with a duplicate.
0
 

Author Comment

by:talekzander
ID: 40576469
Okay.  The error must be some odd thing with my system.  Thank you.  I will give you credit for the code you provided in the beginning.  Thank you for sticking with me and my newness to vba.
0
 

Author Closing Comment

by:talekzander
ID: 40576471
Excellent solution and very patient help for a newbie.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now