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.

 

[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?

 

[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?