Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-23-2017, 02:45 AM   #1
chrisjames25
Newly Registered User
 
Join Date: Dec 2014
Posts: 316
Thanks: 3
Thanked 1 Time in 1 Post
chrisjames25 is on a distinguished road
Check Userform entry not a duplicate

Hi All

First post here and just starting trying to build a stock system at work. Understand a bit about VBA etc form Excel but first project on Access.

Anyhow, my question is this:

1) Is there an accepted method that is best practice for checking that an entry in a textbox on a userform is not duplicating data that already exists in the linked table. FOr example is it is a fastfood table and im adding a new one i dont want to add KFC twice (even though it is finger licking good)

I have seen a method online using dlookup in afterupdate event on the textbox to do this task and i have seen people also say just use the inbuilt "Allow duplicates - No function".

So are either of the above the correct approach or is there a better method.

2)Id dlookup is way to go i have follow up question - Someone i worked with has said that they have read dlookups can be dangerous if there is a chance in the future of moving database to sql. Is this correct?

Thanks in advance for all your help/

chrisjames25 is offline   Reply With Quote
Old 09-23-2017, 03:23 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,195
Thanks: 57
Thanked 2,311 Times in 2,217 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Check Userform entry not a duplicate

Dlookup() will do. So does DCount().

But much better on Table level, the
one you are telling "Allow Duplicates (on index) to No.
So you will not to worry about coding the
validation. Access will do that for you.


You do the validation though on the BeforeUpdate Event of the
control, so you can Cancel the event.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-23-2017, 06:14 AM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,573
Thanks: 13
Thanked 1,461 Times in 1,392 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Check Userform entry not a duplicate

Always set the requirement at the table level. This will have the database engine enforce the constraint so it doesn't matter what causes the update. It could be a form, a query, or some action taken in a different database FE.

The downside is that when the record gets saved, the user will get an Access error message. You can head this off and provide a more user-friendly error message by checking existence yourself. Typically, I use dCount(*) to look up the value and if dCount() returns a value >0 then the value already exists and I display an error message and cancel the update.

If dCount("*", "yourtableorquery", "Somevalue = " & Me.somecontrol) > 0 Then
Msgbox "The value already exists.",vbOKOnly
Cancel = True
Exit Sub
End If

Put the code in the control's BeforeUpdate event. If you also have to ensure that some value is present, you'll need to do that validation in the FORM's BeforeUpdate Event since individual control events do not fire if a control never obtains the focus.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-23-2017, 06:34 AM   #4
chrisjames25
Newly Registered User
 
Join Date: Dec 2014
Posts: 316
Thanks: 3
Thanked 1 Time in 1 Post
chrisjames25 is on a distinguished road
Re: Check Userform entry not a duplicate

Many thanks for your feedback on this and thanks pat for answering a follow up question before I even ask it about getting A more user friendly message than the one access provides.

Any chance you could just expand on what you mean in your last sentence by "control events do not fire if a control never obtains focus"

Cheers
chrisjames25 is offline   Reply With Quote
Old 09-23-2017, 06:39 AM   #5
chrisjames25
Newly Registered User
 
Join Date: Dec 2014
Posts: 316
Thanks: 3
Thanked 1 Time in 1 Post
chrisjames25 is on a distinguished road
Re: Check Userform entry not a duplicate

Also if your suggested the right approach if you are also trying to stop duplicates across two field. I.e. First and last name?
chrisjames25 is offline   Reply With Quote
Old 09-23-2017, 05:35 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,573
Thanks: 13
Thanked 1,461 Times in 1,392 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Check Userform entry not a duplicate

Every control on a form has 17 events. If you tab through the form, each control will fire the GotFocus, LostFocus, Enter, Exit, and the key press events. If you modify the value, the Before and After Update, the Dirty, and Change events fire. The click event fires if you use the mouse to click into a control. The Undo fires if you use the esc key to clear the control or undo the value in code. If in stead of tabbing from control to control, you use the mouse and click into random controls, No event will fire for any control that you don't click into.

The proper event for doing validation at the control level is the BeforeUpdate event. This is the last control level event that fires before Access moves the characters it collected in the .Text property into the .value property. So, if you find an error, you would prevent the input data from moving any further in the process by using:
Cancel = True to cancel the control's BeforeUpdate event. You will continue to get the error message unless you use the esc key to remove the characters you typed or if you are trying to leave the form,Access will ultimately let you leave the form but it will not save the record. If part of your validation is to ensure that someone actually entered a value in a control, you might try to do that in the control's BeforeUpdate event or if you didn't know any better, you might try to use the LostFocus or Exit events. However, if no one tabbed or clicked into the control, none of those events would fire and so your validation code checking for null would not execute and unless you did something to prevent the record from being saved in the FORM's BeforeUpdate event, you would end up saving the record with a null value. Therefore, some validation (including your next question) belongs in the FORM's BeforeUpdate event. This event is the LAST event that fires before a record is actually saved and again, you would use Cancel = True to prevent the bad data from being saved and under some conditions, you might do Me.Undo to back out all changes to the form as well as cancelling the update. This would allow the user to exit gracefully as if he had never made any change at all. I don't recommend using Me.Undo or even Me.somecontrolname.Undo except in specific circumstances not relevant to this discussion.

If you have to perform a validation that involves more than one control such as the combination of first and last name not being duplicated or some date being <= some other date, it would be awkward to do this validation in individual controls. Typically we don't force a user to take a specific path through the controls of a form. It is too much work and a serious PITA if you have to add controls or you want to change the control order so you wouldn't do multi-control validation in control events, you would do it in the Form's BeforeUpdate event.

PS - You probably don't want to force the combination of First Name and Last Name to be unique. I'm not sure what the stats are but it doesn't take a huge population for you to run into trouble with this rule. If you also capture birth date, you might use the three of them and that should be relatively safe.
__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 09-23-2017 at 05:42 PM.
Pat Hartman is offline   Reply With Quote
The Following 2 Users Say Thank You to Pat Hartman For This Useful Post:
chrisjames25 (09-24-2017), Gasman (09-24-2017)
Old 09-23-2017, 06:18 PM   #7
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 716
Thanks: 1
Thanked 218 Times in 204 Posts
moke123 will become famous soon enough
Re: Check Userform entry not a duplicate

Quote:
Also if your suggested the right approach if you are also trying to stop duplicates across two field. I.e. First and last name?
Not to muddy the waters, but, It is possible that you may have 2 people with the same first and last name and then you may need to modify your approach. you may also have to account for spelling variations (ie. Erica/ Erika)

one method I employ is the Levenshtein distance.

the Levenshtein distance between two words is the minimum number of single-character edits (insertions, deletions or substitutions) required to change one word into the other. This is pretty good in catching duplicates while also catching small spelling differences.

When I enter a name I run a procedure which measures the distance between the name and all the other names in the table. If the distance is less than 4, for instance, then i notify the user of possible matches (usually with other info such as Date of birth or city) and allow them to either continue with the entry of the duplicate or choose one of the matches.

moke123 is offline   Reply With Quote
Old 09-24-2017, 12:12 AM   #8
chrisjames25
Newly Registered User
 
Join Date: Dec 2014
Posts: 316
Thanks: 3
Thanked 1 Time in 1 Post
chrisjames25 is on a distinguished road
Re: Check Userform entry not a duplicate

Hi Guys

Many thanks for your posts especially the detailed one above about controls and events Pat. It really made things simple and easy to understand. Will have a play now with my forms and see how i go.

Ill close this thread as solved and add rep.

CHeers
chrisjames25 is offline   Reply With Quote
Old 09-24-2017, 05:57 AM   #9
chrisjames25
Newly Registered User
 
Join Date: Dec 2014
Posts: 316
Thanks: 3
Thanked 1 Time in 1 Post
chrisjames25 is on a distinguished road
Re: Check Userform entry not a duplicate

HI Pat

Apologies for follow up but the Dcount is not working on my VBA. I must have done it wrong. Not sure how to insert the code into this message so apolioges if it goes in against forum regulation.

Code:
If DCount("Category", "Tbl_Category", "Category=" & Txt_NewCategory) > 0 Then
MsgBox "The value already exists.", vbOKOnly
Cancel = True
Exit Sub
End If
THe textbox using is called Txt_NewCategory
THe Table i am using is called Tbl_Category
The Field i am checking against is called Category

Based on the above can you see where my error is occurring. It keeps stating Runtime error 2471.

MAny thansk
chrisjames25 is offline   Reply With Quote
Old 09-24-2017, 06:27 AM   #10
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,669
Thanks: 398
Thanked 636 Times in 617 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Check Userform entry not a duplicate

That syntax works only if the control is numeric. For text you need to surround with a single quote.

So try

Code:
 DCount("Category", "Tbl_Category", "Category='" & Txt_NewCategory & "'") > 0
http://allenbrowne.com/casu-07.html

HTH
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 09-24-2017, 07:38 AM   #11
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 716
Thanks: 1
Thanked 218 Times in 204 Posts
moke123 will become famous soon enough
Re: Check Userform entry not a duplicate

Would a combobox be better for "Categories"?
It would search as you type and you could use the NotInList Event to add the entry if needed.
moke123 is offline   Reply With Quote
Old 09-24-2017, 07:50 AM   #12
chrisjames25
Newly Registered User
 
Join Date: Dec 2014
Posts: 316
Thanks: 3
Thanked 1 Time in 1 Post
chrisjames25 is on a distinguished road
Re: Check Userform entry not a duplicate

Cheers Gasman that solved the problem.

Moke - it may do, hadnt thought of it as the form creating sole purpose is to create new categorys so i instantly assumed would have to type in a textbox
chrisjames25 is offline   Reply With Quote
Old 09-24-2017, 08:39 AM   #13
chrisjames25
Newly Registered User
 
Join Date: Dec 2014
Posts: 316
Thanks: 3
Thanked 1 Time in 1 Post
chrisjames25 is on a distinguished road
Re: Check Userform entry not a duplicate

With the code working once I click ok user form there with duplicate name still in field so I manually have to delete it. I tried code to set it to "" but keeps throwing up an error.

Any suggestions on correct code.
After cancel = true
Code:

Code:
Txt_addCategory = ""
chrisjames25 is offline   Reply With Quote
Old 09-24-2017, 10:30 AM   #14
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,669
Thanks: 398
Thanked 636 Times in 617 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Check Userform entry not a duplicate

Quote:
This is the last control level event that fires before Access moves the characters it collected in the .Text property into the .value property.
From what Pat was saying perhaps you should clear the .Text property?

__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Reply

Tags
dlookup , duplicate data , userform

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Performing a spell check on the contents of a userform field (EXCEL) Rabbitoh Modules & VBA 17 11-14-2018 12:43 AM
Prevent Duplicate Through VBA Userform a_20120 Excel 13 02-11-2010 06:54 AM
Apply Check on Duplicate Entry Waheed2008 Forms 4 08-27-2008 06:10 PM
Check for Duplicate Entry ccflyer Modules & VBA 7 07-30-2007 10:53 AM
VBA: Is it possible to check for a duplicate entry in a table before insert? Argonak Modules & VBA 2 04-18-2005 05:02 AM




All times are GMT -8. The time now is 10:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World