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

Dlookup give san error saying I am missing an operator

P: 9
I am trying to make a login form and used dlookup to confirm that the first name belongs to the employee ID entered.

Dlookup("FName", "UserLogin","[ProcessorID]= '"&me.txtLoginID.value &"'")

I keep getting an error saying I am missing =. Help please.
Mar 2 '17 #1

✓ answered by PhilOfWalton

Solved:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnLoginOK_Click()
  2.  
  3.     Dim FirstName As String
  4.  
  5. If IsNull(Me.txtLoginID) Then
  6.     MsgBox "Please Enter Employee ID", vbInformation, "LoginID Required"
  7.     Me.txtLoginID.SetFocus
  8. Else
  9.     'process the job
  10.     FirstName = Nz(DLookup("[FName]", "UserLogin", "[ProcessorID]= '" & Me.txtLoginID & "'"))
  11.     If FirstName = "" Then
  12.         MsgBox "Sorry, Don't recognise you"
  13.         Exit Sub
  14.     End If
  15.  
  16.     MsgBox "Welcome " & FirstName
  17.     DoCmd.OpenForm "Questionnaire"
  18.     DoCmd.Close acForm, "LoginForm"
  19.     End If
  20.  
  21. End Sub
  22.  
Phil

Share this Question
Share on Google+
16 Replies


PhilOfWalton
Expert 100+
P: 1,430
What sort of field is ProcessorID? I suspect it is numeric.
What is a typical value of Me.TxtLogin? Is it also numeric, if not you might try

Expand|Select|Wrap|Line Numbers
  1. Dlookup("FName", "UserLogin","[ProcessorID]= " & CLng(me.txtLoginID))
  2.  
Also. for clarity, make sure there are spaces on both sides of the "&"

Phil
Mar 2 '17 #2

P: 9
ProcessorID is a text field as well as txtLogin.
I did try that code and it did not work.

Dlookup("[Fname]", "LoginForm", "[ProcessorID]=" & CLng(Me.txtLoginID.Value))

Me entire code is

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnLoginOK_Click()
  2. If IsNull(Me.txtLoginID) Then
  3.     MsgBox "Please Enter Employee ID", vbInformation, "LoginID Required"
  4.     Me.txtLoginID.SetFocus
  5. Else
  6.     'process the job
  7.     Dlookup("[Fname]", "LoginForm", "ProcessorID = '"& txtLoginID.Value &"'")
  8.     MsgBox "Welcome"
  9.     DoCmd.OpenForm "Questionnaire"
  10.     DoCmd.Close acForm, "LoginForm"
  11.     End If
  12. End Sub

If that helps any.
Mar 2 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
OK. Your last code will not work because a DLookup works only on tables and queries, not on a form

So try your original code again but with spaces round the "&" but without the word ".Value"

Expand|Select|Wrap|Line Numbers
  1. Dlookup("FName", "UserLogin", "[ProcessorID]= '" & me.txtLoginID & "'")
  2.  
Also can you confirm that both FName & ProcessorID are in your UserLogin table?

Also I would be rather inclined to run the code on the AfterUpdate of the TxtLoginID rather than on a separate command button.

Unfortunately the code that you have written after the Else clause doesn't really do anything.
It looks up someone's first name and doesn't do anything with it. E.G. compare it to the first name of the person who is logging on, just opens a message box to say "Welcome" which the operator hat to waste their time clicking the OK button, and then does what is wanted by opening the Questionnaire form.

Phil
Mar 2 '17 #4

P: 9
Thank you so much for your help Phil. Unfortunately, it is still giving me the same error. I can confirm that both ProcessorID and FName are in my UserLogin table. My code is as follows:
Expand|Select|Wrap|Line Numbers
  1. Dlookup("[FName]", "UserLogin", "[ProcessorID]= '" & me.txtLoginID & "'")
  2.     MsgBox "Welcome"
  3.     DoCmd.OpenForm "Questionnaire"
  4.     DoCmd.Close acForm, "LoginForm"
  5.     End If 
My intentions for the clause after else is for a pop up to say "Welcome (FName) (LName)." I was thinking that by looking up the values I would be able to input them into the message.
Mar 3 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
Not that I doubt you, but there is something very odd here.

Please can you post your UserLogin table.

If you put a debug stop on the DLookup Line, what value are you getting for the me.txtLoginID.

I am a little suspicious (don't know why) but I would expect the word me. would be capitalised to Me. In all my databases, the editor always capitalises the word Me. It's as if it isn't being recognised.

Phil
Mar 3 '17 #6

P: 9
How would I go about posting my table? I am sorry I am a newbie here.
Mar 3 '17 #7

PhilOfWalton
Expert 100+
P: 1,430
Well you could either do a PrintScreen of tour table in design view and send the image, or better still if you remove any sensitive information from your database, you can send it as a zip file.

That can either be posted on the Bytes website, or if you send me a personal message, we can arrange that you send the zip file directly to me

Phil
Mar 3 '17 #8

P: 9
Here is my table. Let me know if there is anything else you need.
Attached Images
File Type: png UserLogin.PNG (9.2 KB, 52 views)
Mar 3 '17 #9

PhilOfWalton
Expert 100+
P: 1,430
Table looks fine.

So if you debug the code, what value are getting (see my earlier post) for me.txtLoginID

Another way of getting this is on the first line of code type

Expand|Select|Wrap|Line Numbers
  1. Debug.Print me.txtLoginID
  2.  
Phil
Mar 3 '17 #10

P: 9
When I debug it says syntax error.

Would it have to do anything with my textbox being unbound?
Mar 3 '17 #11

PhilOfWalton
Expert 100+
P: 1,430
Sorry, try changing the dot to an exclamation mark !

Expand|Select|Wrap|Line Numbers
  1. Debug.Print me!txtLoginID
  2.  
Phil
Mar 3 '17 #12

P: 9
Here is my file. Let me know what you find.
Attached Files
File Type: zip MPPDB Master - Copy.zip (449.5 KB, 24 views)
Mar 3 '17 #13

PhilOfWalton
Expert 100+
P: 1,430
Solved:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnLoginOK_Click()
  2.  
  3.     Dim FirstName As String
  4.  
  5. If IsNull(Me.txtLoginID) Then
  6.     MsgBox "Please Enter Employee ID", vbInformation, "LoginID Required"
  7.     Me.txtLoginID.SetFocus
  8. Else
  9.     'process the job
  10.     FirstName = Nz(DLookup("[FName]", "UserLogin", "[ProcessorID]= '" & Me.txtLoginID & "'"))
  11.     If FirstName = "" Then
  12.         MsgBox "Sorry, Don't recognise you"
  13.         Exit Sub
  14.     End If
  15.  
  16.     MsgBox "Welcome " & FirstName
  17.     DoCmd.OpenForm "Questionnaire"
  18.     DoCmd.Close acForm, "LoginForm"
  19.     End If
  20.  
  21. End Sub
  22.  
Phil
Mar 3 '17 #14

P: 9
Thank you so much!!!! This is exactly what I wanted! Is there a way to go back to the login form if the user says that is not them?
Mar 3 '17 #15

PhilOfWalton
Expert 100+
P: 1,430
The normal way to use a login form is to ask for 2 bits of information so something like they enter the first name, then enter the number which looks up the first name. If they match go on, if not, clear both fields and try again.

Phil
Mar 3 '17 #16

P: 9
Okay got it! Thank you so much Phil.
Mar 3 '17 #17

Post your reply

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