By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

Checking for duplicate entries

P: 9
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
Feb 12 '07 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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).
Feb 12 '07 #2

ADezii
Expert 5K+
P: 8,597
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?
Feb 13 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 13 '07 #4

P: 9
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Date_of_Birth_AfterUpdate()
  2.  
  3.     Dim DupeVal As Variant
  4.     DupeVal = DLookup ("[Surname]" , "[Forename(s)]" , "[Date of Birth]" Like '" & Me!FieldName) & "'"
  5.     If Not (IsNull(DupeVal)) Then
  6.         Cancel = True
  7.         MsgBox "This user already exists"
  8.     End If
  9.  
  10. 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
Feb 14 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 25 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub FirstName_BeforeUpdate(Cancel As Integer)
  2.   Cancel = DupUser()
  3. End Sub
  4.  
  5. Private Sub LastName_BeforeUpdate(Cancel As Integer)
  6.   Cancel = DupUser()
  7. End Sub
  8.  
  9. Private Sub DOB_BeforeUpdate(Cancel As Integer)
  10.   Cancel = DupUser()
  11. End Sub
  12.  
  13. Private Function DupUser() As Boolean
  14.   Dim strWhere As String
  15.  
  16.   DupUser = False
  17.   If IsNull(Me!FirstName) _
  18.   Or IsNull(Me!LastName) _
  19.   Or IsNull(Me!DOB) Then Exit Function
  20.   strWhere = "(([FirstName]='" & Me!FirstName & "') AND " & _
  21.              "([LastName]='" & Me!LastName & "') AND " & _
  22.              "([DOB]=#" & Format(Me!DOB, "m/d/yyyy") & "#))"
  23.   If Not IsNull(DLookup("[LastName]", _
  24.                         "[MyTable]", _
  25.                         strWhere)) Then
  26.     DupUser = True
  27.     Call MsgBox("This user already exists", _
  28.                  vbExclamation, _
  29.                  "DupUser()")
  30.   End If
  31. End Function
Feb 25 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.