By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

how to check a field from another table

P: 6
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
Mar 4 '14 #1

✓ answered by topher23

Got it. WantedName is a string, correct? Use instead
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. WantedName = 'Billy The Kid'
to the database, which is interpreted as a single string like it should be.

Share this Question
Share on Google+
12 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
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.
Mar 4 '14 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 4 '14 #3

P: 6
Thanks both for your reply!!,

actually i've did with the both function Dcount and Dlookup as following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub PassengerName_Exit(Cancel As Integer)
  2.     Dim StPassenger As String
  3.  
  4.         StPassenger = DLookup("wantedName", "WantedList", "WantedName" = Me.PassengerName.Value)
  5.  
  6.         If StPassenger = True Then
  7.             MsgBox "This Name is in the wanted list .... ;" & StPassenger
  8.             Exit Sub
  9.  
  10.         Else
  11.         End If
  12. End Sub
But it while exit from the field giving a massage Run Time Error 94 Invalid use of Null.

Please advise,
Mar 5 '14 #4

Seth Schrock
Expert 2.5K+
P: 2,931
Your criteria for the DLookup function is flawed. It should be
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 5 '14 #5

P: 6
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/...Passengers.rar
Mar 5 '14 #6

topher23
Expert 100+
P: 234
Your function usage is still flawed. DLookup returns a matching string.
Your code should read:
Expand|Select|Wrap|Line Numbers
  1. Private Sub PassengerName_Exit(Cancel As Integer)
  2. Dim StPassenger As String
  3.  
  4. StPassenger = Nz(DLookup("wantedName", "WantedList", "WantedName = " & Nz(Me.PassengerName.Value))) 'Nz will turn null results into blanks, a good practice to use
  5.  
  6. If Not StPassenger = "" Then 'If it's not an empty string, then a matching name was returned
  7.     MsgBox "This Name is in the wanted list .... ;" & StPassenger
  8.     Exit Sub
  9. End If
  10. 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.
Mar 5 '14 #7

P: 6
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.
Mar 5 '14 #8

Seth Schrock
Expert 2.5K+
P: 2,931
When you get that error message, click debug and then tell us what line is highlighted.
Mar 5 '14 #9

P: 6
this line is highlited :
Expand|Select|Wrap|Line Numbers
  1. StPassenger = Nz(DLookup("wantedName", "WantedList", "WantedName = " & Nz(Me.PassengerName.Value)))
and the value in Stpassenger is ""
Mar 5 '14 #10

topher23
Expert 100+
P: 234
Got it. WantedName is a string, correct? Use instead
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. WantedName = 'Billy The Kid'
to the database, which is interpreted as a single string like it should be.
Mar 5 '14 #11

P: 6
Yaaa now its works !!!! thanks you maaan ,, !!!
Mar 5 '14 #12

Seth Schrock
Expert 2.5K+
P: 2,931
Good catch Topher! I had missed that.
Mar 5 '14 #13

Post your reply

Sign in to post your reply or Sign up for a free account.