Check Userform entry not a duplicate

[chrisjames25]

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/

 

[arnelgp]

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.

 

[Pat Hartman]

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.

 

[chrisjames25]

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]

Also if your suggested the right approach if you are also trying to stop duplicates across two field. I.e. First and last name?

 

[Pat Hartman]

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.

 

[moke123]

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.

 

[chrisjames25]

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]

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.

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

 

[Gasman]

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

So try

 DCount("Category", "Tbl_Category", "Category='" & Txt_NewCategory & "'") > 0

http://allenbrowne.com/casu-07.html

HTH

 

[moke123]

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.

 

[chrisjames25]

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]

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]Txt_addCategory = ""

[/CODE]

 

[Gasman]

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?