Not Being Able To Use DLookup With Dynamic Data...

[Challseus]

Okay, for anyone who hasn't been helping me these past 3 days, here's my story(sorry if this is long, but I had to make sure I was totally understood):

I have a userform with 4 different fields. One of them is called ssn(social security number). After the user types the ssn in, they can move to type in data in another field. At that point, an on_exit function is called on the field, and a query is run on another table to see if the ssn is in it. If not, it will prompt the user to store it in the database, or cancel. Here's my code:

'In Form_Keys
Option Explicit
Private Sub ssn_Exit(Cancel As Integer)
      If Not Form_Students.queryFormStudents(ssn.value) Then
            DoCmd.CancelEvent
      End If
End Sub
.
.
.In Form_Students
Public Function queryFormStudents(ssn As String) As Boolean
      queryFormStudents = True
      Dim answer As Integer

      MsgBox ssn
      MsgBox Form_Students.ssn
      If IsNull(DLookup("ssn", "Students", "[ssn] = ssn)) Then
            answer = MsgBox("SSN doesn't exist.  Create new one?", vbOKCancel)
            If answer = vbOK Then
                  DoCmd.OpenForm "Students"
            Else
                  queryFormStudents = False
            End If
      End If
End Function

Here's the problem...The DLookup is never returning null, even if the ssn the user types is definitely not in the other table. I even used MsgBox's to see what the corresponding values are, and they were in fact different as assumed. Now, for some reason, if I write it like this:

If IsNull(DLookup("ssn", "Students", "[ssn] ='123-45-6789'))

It functions as it's supposed to. So my question is, can you not use variables in DLookup, only actual constant values?

 

[Russell Hauf]

Your DLookup should look something like this:

DLookup("ssn", "Students", "[ssn] = '" & ssn & "'")

However, a better way to do what you are doing would be to make the text box that you are inputting SSN into a combo box (based on all SSN's in your other table) and set the Limit To List property to Yes, then use the NotInList event to handle SSN's that are not in your other table (you could add them via code).

HTH,

Russell

 

[Challseus]

Thanks man, as usual, it worked perfectly. Now on to phase 2 of my project....