how to check a field from another table

[Misab1984]

Hi All,

I've a a main table to enter personal data of PASSENGER, and other table of WANTED LIST, I need while entering the name of the passenger in the main table I need it to check the name first in wanted list table, if the name is existing provide me a massage stating he is in the wanted list,
How I can do that by VBA, please advise dear experts!!,

Many thanks in Advance !!
MIsab

[Seth Schrock]

I would probably use the DCount() function to count the number of records in your "other" table using its WHERE clause to specify that you want only the records where the first name matches. Then if the result is greater than 0 you have at least one match. Here is a link to describe how to use DCount(): DCount Function.

[NeoPa]

DLookup() might make more sense. One would assume that the criteria are unique. If not then it would be necessary to throw up a list of matches anyway, and allow the operator to select or reject the passenger from it.

[Misab1984]

Thanks both for your reply!!,

actually i've did with the both function Dcount and Dlookup as following code:

Private Sub PassengerName_Exit(Cancel As Integer)
    Dim StPassenger As String
    
        StPassenger = DLookup("wantedName", "WantedList", "WantedName" = Me.PassengerName.Value)
        
        If StPassenger = True Then
            MsgBox "This Name is in the wanted list .... ;" & StPassenger
            Exit Sub
            
        Else
        End If
End Sub

But it while exit from the field giving a massage Run Time Error 94 Invalid use of Null.

Please advise,

[Seth Schrock]

Your criteria for the DLookup function is flawed. It should be

StPassenger = DLookup("wantedName", "WantedList", "WantedName = " & Me.PassengerName)

The .Value property of the control is the default, so it isn't needed. The equals sign needed to be inside the double quotes and then the value from PassengerName will be concatenated to your criteria using the ampersand.

You said you used both the DCount and DLookup functions, but I'm only seeing you using the DLookup function. If you did in fact use the DCount function, please provide that section of the code as well so that we can see if the error is in there as well. Also, please use CODE tags (the [CODE/] button at the top of the text editor box) when posting code.

[Misab1984]

I've tried now as you informed me but its giving me error 2001, and msg; you cancelled the previus operations.

I've attached the sample database in the below link, could you please check and let me know,,

https://dl.dropboxusercontent.com/u/30833132/Passengers.rar

[topher23]

Your function usage is still flawed. DLookup returns a matching string.
Your code should read:

Private Sub PassengerName_Exit(Cancel As Integer)
Dim StPassenger As String

StPassenger = Nz(DLookup("wantedName", "WantedList", "WantedName = " & Nz(Me.PassengerName.Value))) 'Nz will turn null results into blanks, a good practice to use

If Not StPassenger = "" Then 'If it's not an empty string, then a matching name was returned
    MsgBox "This Name is in the wanted list .... ;" & StPassenger
    Exit Sub
End If
End Sub

FWIW, DLookup is generally a faster method than DCount, since it stops searching at the first found record rather than going through each record.

[Misab1984]

Dear topher23,

thanks for your reply, I've copied and pasted your code in the database, where it was giving me the same runtime error 2001, msg: you cancelled the previus operations.

[Seth Schrock]

When you get that error message, click debug and then tell us what line is highlighted.

[Misab1984]

this line is highlited :

StPassenger = Nz(DLookup("wantedName", "WantedList", "WantedName = " & Nz(Me.PassengerName.Value)))

and the value in Stpassenger is ""

[topher23]

Got it. WantedName is a string, correct? Use instead

StPassenger = Nz(DLookup("WantedName", "WantedList", "WantedName = '" & Nz(Me.PassengerName.Value) & "'"))

THis wraps the control value in string literals. Otherwise what's being passed to the table looks like

WantedName = Billy The Kid

The table can't parse that statement, since "Billy" would be interpreted as a variable and "The Kid" would just be extra terms. By wrapping the name in string literals, you pass

WantedName = 'Billy The Kid'

to the database, which is interpreted as a single string like it should be.

[Misab1984]

Yaaa now its works !!!! thanks you maaan ,, !!!

[Seth Schrock]

Good catch Topher! I had missed that.