[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Check for duplicate field before saving

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
Terri Alekzander
Asked:
Terri Alekzander
  • 8
  • 7
1 Solution
 
PatHartmanCommented:
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
 
Terri AlekzanderAuthor Commented:
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
 
PatHartmanCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Terri AlekzanderAuthor Commented:
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
 
PatHartmanCommented:
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
 
Terri AlekzanderAuthor Commented:
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
 
PatHartmanCommented:
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
 
Terri AlekzanderAuthor Commented:
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
 
PatHartmanCommented:
So, the code doesn't stop in the procedure or did you remove the stops also?
0
 
Terri AlekzanderAuthor Commented:
I left the stops in. The code didn't stop.
0
 
PatHartmanCommented:
Can you post the database stripped down to the form with the problem (and whatever it needs to support it) if necessary.
0
 
Terri AlekzanderAuthor Commented:
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
 
PatHartmanCommented:
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
 
Terri AlekzanderAuthor Commented:
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
 
Terri AlekzanderAuthor Commented:
Excellent solution and very patient help for a newbie.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now