Checking for duplicate entries

[TonyMacaroni]

Hi everyone,

I have a database I'm helping set up for a Charity - I'd like to create a query where if the admin team tries to enter a member twice, a pop up box will inform them that a member with that information already exists in the database and cancels the record.

Essentially:

After admin enters FirstName, LastName and DOB, the database checks to see if all this information is unique to that one record.

If they are, then proceed, if not, then flag that a user alreadys exists with that info.

Does anyone know if this is doable and also if this is the best way of checking?!

Thanks,

Tony

[NeoPa]

If you have a Form with the data entry facility you can put code in the AfterUpdate event procedures of the three main fields to call another procedure which would check that all fields had been entered. If they had it would use DLookup to check for the pre-existence of a matching record. If found a MsgBox() call could inform the operator to change his entry(ies).

[ADezii]

If you have a Form with the data entry facility you can put code in the AfterUpdate event procedures of the three main fields to call another procedure which would check that all fields had been entered. If they had it would use DLookup to check for the pre-existence of a matching record. If found a MsgBox() call could inform the operator to change his entry(ies).

NeoPa:
Wouldn't a Composite Primary Key on [LastName], [FirstName], and [DOB] be the most straightforward and foolproof approach?

[NeoPa]

That protects the data but means handling the situation for the programmer and the operator is more complicated. That should be done too, I would say, but the question was about how to save trying to update it beforehand, if the data was wrong.

[TonyMacaroni]

Thanks for the info chaps,

I've investigated DLookup and this seems to be the best way forward. I searched the web and found a similar scenario using some DLookup code. Taking what I understood from the person detailing the code, I've mocked up an idea of what I think the code for my scenario should look like:

Private Sub Date_of_Birth_AfterUpdate()
 
    Dim DupeVal As Variant
    DupeVal = DLookup ("[Surname]" , "[Forename(s)]" , "[Date of Birth]" Like '" & Me!FieldName) & "'"
    If Not (IsNull(DupeVal)) Then
        Cancel = True
        MsgBox "This user already exists"
    End If
 
End Sub

I've tested this and it comes up as a Syntax Error on the DupeVal = DLookup line which isn't suprising!

Could someone point me in the right direction as to what is wrong with this line?!

Thanks for your help in advance!

Tony

[NeoPa]

DLookup Function
You can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in Visual Basic, a macro, a query expression, or a calculated control on a form or report.

You can use the DLookup function to display the value of a field that isn't in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the ProductName field is in another table, the Products table. You could use the DLookup function in a calculated control to display the ProductName on the same form.

DLookup(expr, domain, [criteria])
The DLookup function has the following arguments.

Argument Description
expr An expression that identifies the field whose value you want to return. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name for a query that does not require a parameter.
criteria An optional string expression used to restrict the range of data on which the DLookup function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DLookup function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DLookup function returns a Null.

So, if you have a table called [MyTable] and fields in your table called [FirstName], [LastName] & [DOB] and controls on your form also called [FirstName], [LastName] & [DOB] then you would need something like :
TBC - Will try to get to this later. Must go now and this is worth posting anyway.

[NeoPa]

So, if you have the setup described in post #6, the following code should work for you.
You will need to change the names of the various items if they are different of course.

Private Sub FirstName_BeforeUpdate(Cancel As Integer)
  Cancel = DupUser()
End Sub
 
Private Sub LastName_BeforeUpdate(Cancel As Integer)
  Cancel = DupUser()
End Sub
 
Private Sub DOB_BeforeUpdate(Cancel As Integer)
  Cancel = DupUser()
End Sub
 
Private Function DupUser() As Boolean
  Dim strWhere As String
 
  DupUser = False
  If IsNull(Me!FirstName) _
  Or IsNull(Me!LastName) _
  Or IsNull(Me!DOB) Then Exit Function
  strWhere = "(([FirstName]='" & Me!FirstName & "') AND " & _
             "([LastName]='" & Me!LastName & "') AND " & _
             "([DOB]=#" & Format(Me!DOB, "m/d/yyyy") & "#))"
  If Not IsNull(DLookup("[LastName]", _
                        "[MyTable]", _
                        strWhere)) Then
    DupUser = True
    Call MsgBox("This user already exists", _
                 vbExclamation, _
                 "DupUser()")
  End If
End Function