Duplicate name query help?

[rich2912]

I have set up a query to indentify if there is a duplicate entry on a presons name. The query shows the Students forename, surname and their address.

I would like the query to run after the surname field is updated on the data entry form. But I only want it to run when the query has identified that there is a duplicate entry.

Here is the line of code I used in the query under the students forename in the criteria line

In (SELECT [forename] FROM [students] As Tmp GROUP BY [forename],[surname] HAVING Count(*)>1 And [surname] = [students].[surname])

Any ideas of how I can get this to run ONLY when it has identified a duplicate entry?

 

[Len Boorman]

If you are concerned about duplicate Surname why not make this the primary key. That will error immediately.

Would be a bit worried though. Two students with same surname not that unusual is it ?.

Lots of Smith's about

perhaps you need to think on this one

Len B

 

[rich2912]

The query only picks up a duplicate entry when the Forename AND surname are the same (see code) it also then shows you the address so you can make sure that these students don't just share the same full name.

The reason for this is that students may apply for different courses via two or more application forms instead of on the one form.

Thanks

 

[Mile-O]

I wouldn't make either name the primary key...

 

[rich2912]

The query is set up and working, it shows students that appear more than once in the student file, by forename and surname.

I have six databases now that would really benefit from a function that runs the query after the students name has been entered.

In basic terms, it validates more than one field for a duplicate entry. So instead of validating whether "Bob" appears more than once, it validates whether "Bob" & "Thompson" together appear more than once.

I would like the database to run the query after the surname field has been updated, only if the query contents have been changed i.e a new student is added to the query because "Bob Thompson" is already stored in the student file.

The inputter would then be able to check if these are the same person by comapring addresses. If the student is the same, they can find the existing student from the drop down list of students and modify the data for that person.




I appreciate any help given as its probably difficult to understand what I mean by the way I have said it. But I hope that someone can make sense of what I am saying a provide a solution.

 

[Len Boorman]

If you had two combo boxes

Box 1 with Forename
Box 2 with Surname and also address

Providing Limit to List set to No and with sources based of data tables the inputter would be able to see immediately if there was a duplicate Forename/Surname combination and additionally have address details available for checking as well.

If Address is different then continue with the input form adding new address or else escape out

len B

 

[Mile-O]

On the BeforeUpdate of each textbox for forename and surname

If IsNull(Me.txtForename) OR IsNull(Me.txtSurname) Then 
    Exit Sub
Else
    If NameCheck = True Then
        Cancel = True
    End If
End If

Then write a function called NameCheck that returns a boolean value.

Something like:

Function NameCheck() As Boolean
    [i]' code that determines if there is a duplicate[/i]
    If DCount("Forename", "tblPeople", "Forename = """ & Me.txtForename & """") <> 0 And DCount("Surname", "tblPeople", "Surname = """ & Me.txtSurname & """") <> 0 Then
        If MsgBox("Duplicate found. Allow?", vbQuestion + vbYesNo) = vbNo Then
            NameCheck = True
        End If
    End If
End Function

 

[rich2912]

Thanks a lot, that was exactly what I was after!!