Preventing duplicate entry in names in MS Access

[bob fitz]

I'll me more than happy to help if I can. I will take a look tomorrow evening.

 

[RainLover]

Sounds like a Relationship Problem to me.

Could you take a screen dump of you relationships and attach. It will make Bob's job easier. Dumping the screen shot into a Jpeg or even word will be fine. You don't have to show it as a pic here.

 

[coolwiltz@gmail.com]

Hi Bob , Paul and Rainlover

Sorry have been studying which code can be used to solve this hence did not reply. Please see the code which i have used but it doesnt work.. :-( I know it may look a bit haywire but i read books and have tried getting this as i am coding nearly after a decade.... Thank you for helping me...

This does not do what I want. I want the database to prompt the user that the data already exists and if the user wants to edit the existing data or add a new data (new record).... Please help me team... I am unable to sleep bcus of this as i am unable to know how to proceed...

Private Sub Ctl_Lname_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Dim Lname As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
Lname = Me.Lname.Value
Lname = "[Lname]=" & "'" & Marketing & "'"
'Check Marketing table for duplicate entry
If DCount " [Lname]= " & Marketing & " ' AND Fname<> '' & me.Lname
Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning: " _
& Lname & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original record
rsc.FindFirst Lname
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub

 

[RainLover]

Did you read Post #22.

I guess you missed it.

Also rewrite your code and stop when you find the duplicate. Test it several times. Once that works then we can move onto the part where you find the record.

BTW Do you know where your current code fails. If not put in some message boxes. If you get a resutlt then things are fine so you can move further down through the code.

 

[coolwiltz@gmail.com]

Hi Bob and Rainlover

I have used the code which was posted earlier... and it did work perfectly well.

However i wanted to take to next stage where the database after giving a message of duplicate entry I want the access to give me notification as:

"Do you want to edit the duplicate entry or do you want to add a new entry"

and if i choose edit entry - the access should take me to the original entry which was entered earlier and allow me to edit.

and if i choose add new entry it should allow me to enter a new record.

For example:

Tony Hut is entered twice - access should give me options as
"The data already exists - Do you wish to edit the exisiting entry or do you want to add a new entry?"
I click edit button - it should allow me to edit the data
I click add button - it should allow me to add data

Can you help me with this??

Thank you so much...

 

[RainLover]

You still did not answer my question.

All you have done is repeated your problem. So let's try something completely different.

The are a hundred and one ways to skin a cat. Make you might like this one. (Make sure your knife is sharp)

Create a completely new Form with the data you want to display. Either Datasheet or Continuous. You can pretty this up later. In the query for this New Form have the criteria set to the Control/s from the Main Form. (The Form we are trying to fix)

Place a Temporary Open Form button on the main Form. If you have done this correctly it should open the New Form showing All/Any record that matches your criteria.

Rem out all the Code in the main form that has anything to do with the new form.

Back to the Main Form

Now run a DCount on the criteria in question. If DCount > 1 then Message box with the result. Finalise this routine with a End Sub. We are ignoring errors at this stage.

You should now have your Main form with the Criteria showing.

Click on the Open New Form Button.

This is totally untested but with a few refinements I see no reason why it can't be made to work.

 

[cstuckey56]

I Used Bob's code to prevent duplicate entries in my form but it doesn't appear to be working. Am I doing anything wrong?

I'm using my table name and First and Last name in replace of the example above
"yourTableNameHere" = "Clients"
[FName] = "FirstName"
[LName] = "PreferredName"

Private Sub Ctl_Lname_BeforeUpdate(Cancel As Integer)
Dim dupCount As Long
dupCount = DCount("*", "yourTableNameHere", "[LName]= '" & Me.[LName] & "'" & " And " & "[FName] = '" & 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

thanks
Chris