Solved No.2 - Again - Avoid Duplicate Records - Using Form-validation - Using two controls -LastName & FirstName

[AccessAllAreas]

Dear Experts

Thank you for your patience.

I have another issue when trying to implement form-level validation. This is a bound form that can both be used to displat records and input new infor ation for volunteers.

Goal: To avoid duplicate records by comparing LastName concatenated with the entry for FirstName, but then, After a MessageBox has been shown and OK selected, the duplicate record is displayed for the purposes of comparison.

Form:
Relevant Controls:
VolunteerId (referr to field in table contain Primary key)
LastName
FirstName

Existing VBA code (I have taken it from the web - it manages to get to the point of recognising the duplicate record, but does not display the exisiting record that the LastName and FirstName combination matches):

Private Sub Volunteer_First_Name_BeforeUpdate(Cancel As Integer)
Dim NewVolunteer As String
Dim strLinkVolunteerCriteria As String
Dim VolunteerNo As Integer
NewVolunteer = Me.Volunteer_Surname.Value & Me.Volunteer_First_Name.Value
strLinkPublisherCriteria = "'" & NewPublisher & "'"
  If (Me.Volunteer_Surname & Me.Volunteer_First_Name) = DLookup(("[Volunteer Surname] & [Volunteer First Name]"), _
    "Tab_Emergency_Contact_List", strLinkVolunteerCriteria) Then
        MsgBox "The publisher name that you are trying to add, " & NewVolunteer & ", already exists in the database." _
        & vbCrLf & vbCrLf & "Please check the record displayed to confirm whether you wish to add this person as a unique individual." _
        & vbCrLf & vbCrLf & "Please make the Surname and First Name combination are unique compared with existing publisher record.", _
        vbInformation, "Duplicate Volunteer Record"
       Me.Undo
    Else

        DoCmd.RunCommand
    End If
VolunteerNo = DLookup("VolunteerID", "Tab_Emergency_Contact_List", strLinkVolunteerCriteria)
Me.DataEntry = False
Me.PublisherID.SetFocus
DoCmd.FindRecord PublisherNo, , , , , acCurrent
End Sub

Once again, thanks in advance. I am sure it js easy. But beyond me, I am afraid.

 

[CJ_London]

don't know where newPublisher comes from and your naming is all over the place. You say

Form:
Relevant Controls:
VolunteerId (referr to field in table contain Primary key)
LastName
FirstName

but then in your code you are using

Me.Volunteer_Surname.Value & Me.Volunteer_First_Name.Value

I would use

If DCount("*","Tab_Emergency_Contact_List", "[Volunteer Surname]='" & Me.Volunteer_Surname & "' AND [Volunteer First Name]='" & Me.Volunteer_First_Name & "'")>0 then

 

[AccessAllAreas]

You are right.

Many apologies. By trying to make my naming clearer (to others before this posting), i made it much worse. Sorry. Next time, I will do better.

I will give your kind suggestion a go and let you know. (I hope it is that easy!)

 

[Gasman]

You are right.

Many apologies. By trying to make my naming clearer (to others before this posting), i made it much worse. Sorry. Next time, I will do better.

I will give your kind suggestion a go and let you know. (I hope it is that easy!)

Display what you actually use, with copy and paste.

Too many people rewrite/change the code (to make it easier supposedly) so others can understand the issue, and at the same time make errors or obfuscate the issue even more.

 

[AccessAllAreas]

Once again, dear Gasman and CJ_London, you are correct. I will attempt to do better.

I tried the code that CJ_London kindly posted.

It runs, but for some reason, just as was previously happening, it is is loading the first record in the table (ID No.2), rather than the ID of the record that was being duplicated!

I had to REM out the following statement, as it was throwing up another run-time error (will re-insert and check the number):

' Me.PublisherID.SetFocus

Here is the latter part of the code as it stands:

PublisherNo = DLookup("PublisherID", "Tab_Emergency_Contact_List", strLinkPublisherCriteria)
Me.DataEntry = False
' Me.PublisherID.SetFocus
DoCmd.FindRecord PublisherNo, , , , , acCurrent

 

[Gasman]

DLookup will return the first record that matches your criteria?
When you add a record, that ID should not have been saved, so you should find the original record which makes the one you are adding a possible duplicate.?
Otherwise add to the criteria that the ID is not equal to the current ID.?

Saying that however, I see that CJ_London used Dcount?, and that just returns a count?, you would have no idea which record is involved in that count. So I am not sure what you are trying now.?

 

[AccessAllAreas]

Hi

OK, here is the code, as it stands in the VBE:

Private Sub Publisher_First_Name_BeforeUpdate(Cancel As Integer)
Dim NewPublisher As String
Dim strLinkPublisherCriteria As String
Dim PublisherNo As Integer
NewPublisher = Me.Publisher_Surname.Value & Me.Publisher_First_Name.Value
strLinkPublisherCriteria = "'" & NewPublisher & "'"
  If DCount("*", "Tab_Emergency_Contact_List", "[Publisher Surname]='" & Me.Publisher_Surname & "' And [Publisher First Name]='" & Me.Publisher_First_Name & "'") > 0 Then
  '  If (Me.Publisher_Surname & Me.Publisher_First_Name) = DLookup(("[Publisher Surname] & [Publisher First Name]"), _
   ' "Tab_Emergency_Contact_List", strLinkPublisherCriteria) Then
        MsgBox "The publisher name that you are trying to add, " & NewPublisher & ", already exists in the database." _
        & vbCrLf & vbCrLf & "Please check the record displayed to confirm whether you wish to add this person as a unique individual." _
        & vbCrLf & vbCrLf & "Please make the Surname and First Name combination are unique compared with existing publisher record.", _
        vbInformation, "Duplicate Publisher Record"
        Me.Undo
    Else

        DoCmd.RunCommand acCmdSave
    End If
PublisherNo = DLookup("PublisherID", "Tab_Emergency_Contact_List", strLinkPublisherCriteria)
Me.DataEntry = False
' Me.PublisherID.SetFocus
DoCmd.FindRecord PublisherNo, , , , , acCurrent
End Sub

Both the First If DLookup statement and the suggested If DCount statement initiate the MessageBox, which contains the concatenated string.

What happens after that is a record is returned, but not the record for the PublisherID that is the one that is being duplicated. it is always the very first record in the table, PublisherID No.2.

Hope I have been clear!

 

[Gasman]

If you have a dupe, you need to exit the code not just run through the rest of it?

If you have the PublisherID, you can just find the record with that.?

If you are trying to get the first record that makes your current a possible dupe then use DLookup() to get that ID and then findfirst using that.?

I think you are over complicating it. If you find a dupe, then the user needs to amend that record?, else use the original publisher entry.?

 

[AccessAllAreas]

Thansk for the thoughts, Gasman.

If a duplicate name is entered, the organisation involved wishes to:
(a) check if the person that is having their details inserted at this time is indeed the same person that has already been recorded in the DB. If they are indeed the same person, then looking up and displaying the previously stored record will allow that to the determined.
(b) If the person is not the same person, displaying the already stored record of the person with the same name would again allow easy assessment to determine that they are not the same person and then determine how they might need to change the name of this new-to-the-DB person so that they can be uniquely identified by Surname&FirstName combination.

I hope I have explained clearly why I wish to incorporate this function into this form.

I have seen this work on a web video. I thought that I would be able to adapt it to my needs. But obviously not, which is why I am here asking for help with this.

Any help greatly appreciated, as always. Again, I am happy to be treated as an idiot. Feel free to tell me exactly how to implement any suggestions!

 

[Gasman]

OK, then I believe you need a Dlookup to get the ID if there is a dupe.
Then what do you do with it?
I might open a popup form frmDuplicate with that ID and show whatever you need on that form.
Then depending on whether the user selects it is a dupe or not, you close the form and carry out the relevant processing.?

That would be one way, using a global variable or tempvar to hold the choice of the user.?

 

[Cronk]

I might open a popup form frmDuplicate ....

That was my thought when I first saw that it was only a message warning. With a larger number of records there is also the possibility of more than one match. The popup could display address, phone number, email address... to help determine if a duplicate is about to be added.

 

[moke123]

heres what I use for names to avoid dupes.
It covers much of whats already been mentioned and spelling mistakes.

 

[AccessAllAreas]

Hi

Many thanksnfornthe suggestions. And thanks to moke123 form the sample DB. Wow! I have a feeling I have a lot to learn before I know how to implement that level of sophistication in this DB I am working on!! I am not even sure which parts I need to extract and adapt to use for my purposes!
I have a lot of reading to do!

 

[Gasman]

Hi

Many thanksnfornthe suggestions. And thanks to moke123 form the sample DB. Wow! I have a feeling I have a lot to learn before I know how to implement that level of sophistication in this DB I am working on!! I am not even sure which parts I need to extract and adapt to use for my purposes!
I have a lot of reading to do!

Yes, Access seems so easy, when you can create a few forms with a few tables quick enough.
However as you have found out, when you want to use it a bit more seriously/professionally there is a lot more to learn.
You are not the first and certainly will not be the last.

Good luck with it anyway.

 

[AccessAllAreas]

Hello All (especially those who have offered suggestions to this topic already!)

To my delight, I managed to get this working!

Previously, to stop people fiddling around with the ID field (primary Key) on the form, I had set the enabled property to that particular text box to False.

Additionally, I set this event to After_Update, rather than Before_Update.

Once I had done this, it started working! It is worth noting that not only must the text box for the field be enabled, but it also must not be locked! Had to sort out a few other anomalies with other code running on different events, but that is all fixed now too.

If you want to see the code for reference, see below. It might help someone else.

Dim NewPublisher As String
Dim strLinkPublisherCriteria As String
Dim PublisherNo As Long
' Create variable that contains a concatenated value derived from the values in both Surname and First Name fields
NewPublisher = Me.Publisher_Surname.Value & Me.Publisher_First_Name.Value
' Create temp variable that temporarily stores a value to later use in Dlookup function to lookup PublisherID for matching variable value
strLinkPublisherCriteria = "[Publisher Surname] & [Publisher First Name] =" & " '" & NewPublisher & "'"
' If a value is located in table where Surname and First Name concatenated combo finds an existing record that matches, show Msgbox and Undo
If DCount("*", "Tab_Emergency_Contact_List", "[Publisher Surname]='" & Me.Publisher_Surname & "' And [Publisher First Name]='" & Me.Publisher_First_Name & "'") > 0 Then
        MsgBox "The publisher name that you are trying to add, " & NewPublisher & ", already exists in the database." & vbCrLf & vbCrLf _
        & "When you press OK, the existing record with the name " & NewPublisher & " will be displayed.  " _
        & "Please check the record displayed to confirm whether you wish to add this person as a unique individual.  " _
        & "Perhaps you added them previously and just forgot?" & vbCrLf & vbCrLf _
        & "If you do wish to add them, please make the Surname and First Name combination of any person are unique compared " _
        & "with any existing publisher record.  Perhaps add a middle name or a Suffix", _
        vbInformation, "Duplicate Publisher Record"
        Me.Undo
    Else
        Exit Sub
    End If
' If a duplicate Surname and First Name combo have been located, locate equivalent record and determine it's PublisherID
PublisherNo = DLookup("[PublisherID]", "Tab_Emergency_Contact_List", strLinkPublisherCriteria)
' Set form data entry mode to False
Me.DataEntry = False
' If PublisherID control is disabled, enable it
If Me.PublisherID.Enabled = False Then
Me.PublisherID.Enabled = True
End If
' Locate existing record that was about to be duplicated and display for inspection
DoCmd.FindRecord PublisherNo, , , , , acCurrent
' If PublisherID control is enabled, disable it
If Me.PublisherID.Enabled = True Then
Me.PublisherID.Enabled = False
End If

End Sub

2 major issues down! 5 to go!

 

[Cronk]

Doesn't the

me.Undo

remove the duplicate record before the user is asked? Maybe

if msgbox(....) = vbno then
  me.undo
else

endif