1
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 :)
