468,549 Members | 2,383 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,549 developers. It's quick & easy.

Duplicate Query from form

My problem is a bit long and involved, but I will try to summarize. I have created a database using Access 2000 for a cemetery and it now is populated with almost 6,000 names. I created a form so entries could easily be put in the table. In the Before_Update, I put this code to search for duplicates:

'check if last name, first name, and birth date are matched
If (Not IsNull(DLookup("LName", "Burials", "LName ='" & Me!LName & "'"))) _
And (Not IsNull(DLookup("FName", "Burials", "FName ='" & Me!FName & "'"))) _
And (Not IsNull(DLookup("BDate", "Burials", "BDate ='" & Me!BDate & "'"))) Then

'let user know name already exists
MsgBox "Name has already been entered.", vbInformation, "Duplicate Information"
Cancel = True

The problem with it is, it looks over the entire db everytime it sees a DLookup function. For example, let's say the person's name is Dorothy White and she was born on 1/30/1925. This code searches through the db and finds any matches for "White", then searches it again for first name of "Dorothy", then does it again for that birth date. I need the search to be in these three fields, because the primary key is an autonumber, and what are the odds any two ppl would have the same first and last name AND birth date.

I tried to change a duplicate query which I created with the wizard to reflect values found on the form:

SELECT First(Me!Burials.LName) AS [LName Field], First(Me!Burials.FName) AS [FName Field], First(Me!Burials.BDate) AS [BDate Field], Count(Me!Burials.LName) AS NumberOfDups
FROM Burials
GROUP BY Burials.LName, Burials.FName, Burials.BDate
HAVING (((Count(Me!Burials.LName))>1) AND ((Count(Me!Burials.BDate))>1));

I saved this query under the name Duplicate Query From Form. I want to plug it in using VBA in the Before_Update, and to recognize there is a return value, and give error msg accordingly. The problem is, I cannot figure out how to code this within the Before_Update module.

Am I just way off track in how I can accomplish this? I would GREATLY appreciate any help you could give :)
Jun 28 '07 #1
1 1577
14,534 Expert Mod 8TB
You can set more than one set of criteria on a DLookup and it doesn't matter in this case which value is returned.

Expand|Select|Wrap|Line Numbers
  1. 'check if last name, first name, and birth date are matched
  2.    If nz(DLookup("LName", "Burials", "LName ='" & Me!LName & "' And  FName ='" & Me!FName & "' And BDate ='" & Me!BDate & "'"), "") ="" Then
By the way is BDate a string? If not, if it is a date then replace the single quotes with #
Jul 4 '07 #2

Post your reply

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

Similar topics

4 posts views Thread by afr0ninja | last post: by
8 posts views Thread by sixdeuce62 | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.