Preventing duplicate entry in names in MS Access

[coolwiltz@gmail.com]

Hi Team

I am facing a problem - I want to stop duplicate entries from being entered on form. I have read through the thread , however I am totally confused as it seemed to be v high level complex queries. I am looking at:
Preventing duplicate entries to be entered
It should show an error "Saying entry already exists" Do you want to check,edit or add new...
Can you please help me..

 

[bob fitz]

Can you post a link to the thread you have been looking at?

It should show an error "Saying entry already exists" Do you want to check,edit or add new...

What should show this?

 

[RainLover]

Normally these things are done at the Table level.

But if you follow Bob's advice you should sort it out.

 

[coolwiltz@gmail.com]

Hi Bob

I have added the files for your reference. Do let me know how to proceed..
Please help me with coding while entering first name and last name.. basically it should prompt the user that the name is already existing, hence the user has 3 choices - to check if the data is same or not (view data), if different then add this as new / if same then edit if necessary.
The form should show the error to the person who is doing the data entry.

P.S: It is not allowing me to post the link - the link is in this website ends with
?t=167942

 

[pr2-eugin]

I was hoping to see a Dummy version of your DB in the Attachment.. Is that possible? Because we would like to see some code that you have tried.. For which we could add some suggestions..

 

[coolwiltz@gmail.com]

Hi Paul
My code is as follows:
Private Sub Ctl_Lname_BeforeUpdate(Cancel As Integer)
Dim LName As Variant

LName = DLookup("[txtLName]", "[txtLName]= '" & Me![LName] & "'" & " And " & "[txtFName] = " & Me![FName])

If Not IsNull(LName) Then
Beep
MsgBox "This name already exists in the database. Please check that you are not entering a duplicate person before continuing.", vbOKOnly, "Duplicate Value"
Cancel = True
End If

End Sub


Not sure what is going wrong....

 

[pr2-eugin]

Your Code is a bit off.. The DLookUp statement takes in three arguments, The field to Look for (first argument), the domain/table to look into (second argument), the condition to check for (third optional argument).. Also you have not enclosed the First name in Single quotes..

Also you can use DCount in this instance, which is similar, but would be more appropriate here..

Private Sub Ctl_Lname_BeforeUpdate(Cancel As Integer)
    Dim dupCount As Long
    dupCount = [URL="http://www.techonthenet.com/access/functions/domain/dcount.php"]DCount[/URL]("*", "[COLOR=Red][B]yourTableNameHere[/B][/COLOR]", "[txtLName]= '" & Me.[LName] & "'" & " And " & "[txtFName] = '" & Me.[FName] & "'")
    If dupCount <> 0 Then
        Beep
        MsgBox "This name already exists in the database. Please check that you are not entering a duplicate person before continuing.", vbOKOnly, "Duplicate Value"
        Cancel = True
    End If
End Sub

 

[coolwiltz@gmail.com]

Hi Paul
I cannot thank you enough for guiding me in this... very grateful to you...
I used Dcount and coding as suggested, however I am still facing errors as given below
It gives an error as "you cancelled the previous operation"
Would you be able to let me know why this occurs... Many many thanks for your help!

 

[pr2-eugin]

Is there any other operation that is depending on this Control? Since we have cancelled the event the Error will be thrown, It can be suppressed using a simple Error Handler, however it depends how it would affect the entire logical flow.. Step through your code (by setting breakpoints) to see where you get the error..

 

[bob fitz]

Excuse me butting here, but I think it needs to be:

Private Sub Ctl_Lname_BeforeUpdate(Cancel As Integer)
    Dim dupCount As Long
    dupCount = [URL="http://www.techonthenet.com/access/functions/domain/dcount.php"]DCount[/URL]("*", "[COLOR=red][B]yourTableNameHere[/B][/COLOR]", "[[B][COLOR=darkorange]LName[/COLOR][/B]]= '" & Me.[LName] & "'" & " And " & "[[COLOR=darkorange][B]FName[/B][/COLOR]] = '" & Me.[FName] & "'")
    If dupCount <> 0 Then
        Beep
        MsgBox "This name already exists in the database. Please check that you are not entering a duplicate person before continuing.", vbOKOnly, "Duplicate Value"
        Cancel = True
    End If
End Sub

because LName and FName are the actual field names in the table.

 

[coolwiltz@gmail.com]

Hi Paul
Its getting complicated for me.. Not sure what I am suppose to do next.. sorry to bother u so much but unable to find solution... Grateful if you could help me in solving this..

 

[pr2-eugin]

because LName and FName are the actual field names in the table.

You are right Bob, I just expanded on the Code, I just checked the Document after your post.. Your code is how it should be..

 

[bob fitz]

Hi Paul
Its getting complicated for me.. Not sure what I am suppose to do next.. sorry to bother u so much but unable to find solution... Grateful if you could help me in solving this..

You could try using the code I posted.

 

[pr2-eugin]

Hi Paul
Its getting complicated for me.. Not sure what I am suppose to do next.. sorry to bother u so much but unable to find solution... Grateful if you could help me in solving this..

So where is the problem now? Did you manage to step through your code?

Have you tried Bob's code?

 

[RainLover]

Why are you guys not suggesting a Unique Index at the Table level.

Sorry for butting in.

 

[bob fitz]

In case the user has more than one person with the same name.

 

[bob fitz]

Sorry for butting in.

BTW. No need to apologise Rain. It's always a pleasure to hear from you.

 

[coolwiltz@gmail.com]

Dear Bob

thanks a ton.... You are a true genius.. Wow.. the code that you suggested it did work... yeppieee... it working fine.... Thank you thank you thank you.. cant thank you enough....

Other team members

thank you for your suggestions as well.. However Bob's coding worked...

 

[coolwiltz@gmail.com]

Hi Bob

Now i am running into a silly error.. it does not involve coding, however I am sure I am making some mistake.
Ok my question is when i enter data in form and save and then go to next form some fields such as age/occupation which i selected for the first customer is visible and if i change the second record the first record also changes.
For example on the data entry form front:
I enter Fname : Sonu
Lname: Hemanii
Age as 26-30
and complete form with other details and save and then go to next form
and enter data as:
Fname: Nim
Lname: Shah
Age: 21-23
The data in the first record changes its age to 21-23 and doesnt retain that entry...
What could be problem. I checked the table ... can you help me with this please.....

Many thanks in advance to an expert who can work wonders with access....

 

[coolwiltz@gmail.com]

Hi Bob

for the above query i figured it out n its working fine... but then it does not allow me to view/edit if duplicate entry exits. Example

If the entry is duplicate then
View / Edit / Add new button should show
and it should do that operation...


Can you help me with this??? Is that possible... Please do let me know...

Many thanksss.....