Question about the efficiency of some code.

[nakedbamboo]

I am trying to create some code that will search records to see if a person has already been input into the database. I have got the following code to do what I am trying to do, but I am curious if it is the most efficient way. I have a gut feeling it will be slow as the database becomes large. Is there any other way to do this?

Form name - Alumni
Field names - LastName and FirstName
The following code is in the Form_BeforeUpdate event:

If (Not IsNull(DLookup("[LastName]", _
"Alumni", "[LastName] ='" _
& Me!LastName & "'&quot)) Then
If (Not IsNull(DLookup("[FirstName]", _
"Alumni", "[FirstName] ='" _
& Me!FirstName & "'&quot)) Then
If MsgBox("This First and Last name have already been entered in the database. Do you wish to add this record?", vbYesNo, "Warning&quot = vbNo Then
Cancel = True
Me.Undo
Else
End If
Else
End If
Else
End If

 

[CajunCenturion]

I don't think this is going to work because you are independantly checking the Firstname and the Lastname.

For Example: Suppose you have the following two people in the database:
John Smith
Bob Jones

If you try the Add "John Jones", you will get the message box because the Last Names 'Jones' is in the database, and the FirstName 'John' is in the database, but I don't think that it what you're after. I think you will need to combine your DLookup's into 1 statement with an And for this check to work.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MakeItSo]

Hi nakedbamboo,

Here's an approach I have not fully tested, but it should work:

Dim rs as Recordset

Set rs=CurrentDB.OpenRecordset("SELECT LastName, FirstName FROM Alumni WHERE LastName='" & Me.LastName & "' AND FirstName='" & Me.FirstName & "';&quot

If rs.RecordCount > 0 then
If MsgBox("This First and Last name have already been entered in the database. Do you wish to add this record?", vbYesNo, "Warning&quot = vbNo Then
Cancel = True
Me.Undo
End If
rs.Close

Does that help you?
Regards,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[nakedbamboo]

Thanks guys. It works now. I knew there had to be something wrong with it, I just hadn't tried CajunCenturion's case.