Help with DLookup (Type Mismatch)

[ErikRP]

I'm trying to figure out a problem I'm having with DLookup, and I think I've hit the limit of my abilities.

I've created a form (frmMain) that is used to enter student records. What I'm wanting to do is to check if a student already exists in the database. Rather than wait until the whole record is entered, I want to validate that the last name and first name do not already exist for the same record. E.g. I can have multiple "Johns" in the table, and multiple "Smiths", but I can only have one record with First_Name = "John" and Last_Name = "Smith". (Yes, I realize there could be two different John Smiths at some point - I'll worry about that another time!)

I've got the following code but when I go to run it I get "Run-time error 13: Type Mismatch".

-------------
Private Sub First_Name_AfterUpdate()

Dim fName As String
Dim lName As String
Dim NameResult As String

fName = Forms!frmMain![First Name]
lName = Forms!frmMain![Last Name]

If IsNull(DLookup("[Student Number]", "[Student Info]", "[Last Name] ='" & lName & "'" And "[First Name] ='" & fName & "'")) Then

NameResult = "NEW"
Else
NameResult = "EXISTS"
End If
End Sub
---------

To confirm, the name fields are both text fields. Names are entered in Last Name, First Name order, which is why I am validating after updating the First Name field.

I've added a Watch, and I can see that the variables lName and fName are being updated correctly, but I am receiving the mismatch error. More frustrating is that if I cut down the DLookup code to validate only the First Name or Last Name, I don't receive an error - although it's not useful since I'm only confirming the existence of the First Name or Last Name, but not both.

I don't know if it's the "ISNULL" or I've got the syntax of the DLookup wrong or if there is something else going on.

Any help would be MUCH appreciated!

 

[Brianwarnock]

Admission.
I'm no expert at Dlookup and have not done this
But I would expect to have to do 2 Dlookups to return the 2 separate fields and proceed with the tests from that basis.

Brian

 

[ErikRP]

Thanks for the input Brian, but if I run two separate DLookups, doesn't that create the situation where I am checking to see if there is already a First Name of "John", and then I am checking (separately) to see if there is already a First Name of "Smith"? I don't care if there is already a John Brown, or a Maggie Smith. I only want to verify if John Smith already exists.

I have indexed these two fields such that when the user tries to save the form they will receive an Access error informing the user that there already exists a student with the same first and last name. What I was hoping to do was a) give the user a "softer" warning message and b) present it sooner, so they weren't typing out all sorts of other information.

I have seen some examples of DLookups with multiple criteria but invariably the multiple criteria includes one or more numeric fields. I am 90% sure I have the syntax correct, but it's possible I have a quote in the wrong order, and it's messing up the statement. Either that or I'm misunderstanding how the ISNULL should be used.

I *think* I can do what I want with (almost) what I have now, but then again I might be totally wrong!

 

[Brianwarnock]

You are absolutely correct and I suspect that the process is not simple, and that you would need to check that no Smith with a first name of John exists.
Of course I could be wrong about this maybe another poster will come in and comment.

Brian

 

[Brianwarnock]

Having refreshed myself on Dllokup I think that you should be able to do what you are attempting, so what is going wrong I wonder.

Brian

I'm a lot of use aren't I

 

[Brianwarnock]

OK another wild guess should you have and & at the end, also do you need them round the AND? I have not seen multiple criteria so just guessing as to how I would attempt a few tries.

Brian

 

[missinglinq]

These things are always hairballs, so I just keep an exemplar and cut and paste. This should do the job:

If IsNull(DLookup("[Student Number]", "[Student Info]", "[Last Name]= '" & lName & "' And [First Name] = '" & fName & "'")) 
Then

 

[ErikRP]

That's exactly what I needed - thanks missinglinq! I'll be sure to keep this little sample handy in future!

Thanks too Brian for your assistance - it was reassuring to know I was close!

 

[missinglinq]

Glad we could help!