Solved

Dlookup problem

Posted on 2004-09-27
18
654 Views
Last Modified: 2009-07-29
Hi,

I am trying to retrieve a text string within a table using Dlookup. It is for a password entry field. Goes like this...:
==================================
Private Sub Command3_Click()
Dim StrPersPass As String
Dim StrPassword As String

StrPersPass = DLookup("Password", "tblpasswords", "Function" = "PersonnelPassword")

If IsNull(Me.PasswordBox) Then
    MsgBox "You must enter a password, or Cancel", vbOKOnly
    Me.PasswordBox = ""
    Me.PasswordBox.SetFocus
Else
    StrPassword = Me.PasswordBox
   
If StrPassword = StrPersPass Then
    DoCmd.OpenForm "frmPersonnelInformation"
    DoCmd.Close acForm, "frmPersonnelPassword"
Else
    MsgBox "Your password is incorrect and your attempt has been logged.", vbOKOnly, vbCritical
    'MsgBox StrPersPass
    DoCmd.CancelEvent
End If
End If
End Sub
=========================================

When I run the event, I get error 94 'Invalis use of Null'. For some reason strPersPass returns a value of Null. All fields are text format. I placed the MsgBox StrPersPass to return the value of strPersPass, and the message box is always blank!!

If I change the syntax to: StrPersPass = DLookup("Password", "tblpasswords", "Function=" & "PersonnelPassword") I get Runtime error 2471 -'The object doesn't contain the Automation object "PersonnelPassword"'

All the names are correct, and the table has the relevant infomation... What am I doing wrong...?!?

LoveToSpod
0
Comment
Question by:LoveToSpod
  • 7
  • 7
  • 3
  • +1
18 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 12159365
Hello LoveToSpod,

Change the double quotes around PersonnelPassword to single quotes.

Pete
0
 

Author Comment

by:LoveToSpod
ID: 12159397
Can you edit, and show me:

Cherrs
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12159529
What is PersonnelPassword? Is it a variable or a control on the form? If so, do this:

DLookup("Password", "tblpasswords", "Function='" & PersonnelPassword & "'")

(this is what Pete57r was suggesting)
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 16

Expert Comment

by:Nestorio
ID: 12159534
Try this:

StrPersPass = DLookup("Password", "tblpasswords", "Function = '" &  PersonnelPassword & "'")
0
 

Author Comment

by:LoveToSpod
ID: 12159605
Still getting invalid use of null with new syntax.

PersonnelPassword is a value within the table. Let me layout:

tblPasswords:

Function                    |    Password
---------------------------------------------
PersonnelPassword    |    privateinfo
ReportsPassword       |    printme

Note the Fields 'Function' and 'Password' are text fields.

If there is a better way of recalling the value within the table, or checking the value of PasswordBox then please let me know, though I sure this method should work.

Please help!!!

Cheers,

LoveToSpod


0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 250 total points
ID: 12159743
Oh, I see. Then you need this:

DLookup("Password", "tblpasswords", "Function='PersonnelPassword'")
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12159749
If I understand it would be:

StrPersPass = DLookup("Password", "tblpasswords", "Function = PersonnelPassword")
0
 

Author Comment

by:LoveToSpod
ID: 12159802
Shane,

Once again you got it right. The others all returned run-time errors.

Please can you provide a short explanation as to why these slight differences are so crucial.... And where to use single, double, no quotes, or [x] brackets.

This would really help me.

Thanks...

LoveToSpod
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12159853
Sorry, I've forgot the single quotes surrounding the literal 'PersonnelPassword'.
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 250 total points
ID: 12159880
Sure - in my expression you want to return the Password field where the Function field is equal to PersonnelPassword. so we just embed the actual value into the third parameter (the criteria). In my first example, I had assumed that PersonnelPassword was a variable.

You need to think about what your expression actually evaluates to. This:
"Function"="PersonnelPassword"
is a boolean expression which says "Is the word Function the same as the word PersonnelPassword" (which it obviously isn't), so the expression evaluates to the value False, which is what is passed to the DLookUp.

This:
"Function='PersonnelPassword'"
is a string expression, which is what we want to pass.

Use [] whereever you are referring to a field or table name that has a space or other unusual characters (such as hash or minus signs).

BTW, you will sometimes see people writing things like this:
"Function=" & "'PersonnelPassword'" & ""

This is exactly equal to "Function='PersonnelPassword'" - it's just joining lots of strings together to end up with the same end result, so it's pointless. Only use the concatenation syntax if you want to include a variable or other function value in the string.
0
 

Author Comment

by:LoveToSpod
ID: 12159929
Shane gets 250 points for the right answer, BUT before I Accept as answer, a quick releted question for more points (I'll split them);

Using the above details, how do I now change the value of "Password" from an unbound text field in a different form? The new password will be keyed into: frmPersPassChange![Text2]

Cheers,

LoveToSpod
0
 

Author Comment

by:LoveToSpod
ID: 12159949
Shane, Thanks for the above info on quotes " ' & etc...

I will paste that into my reference files!!

Really appreciated.
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12160083
Try this,

update tblPasswords set Password = Forms!frmPersPassChange![Text2]
where Function = "PersonnelPassword"
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12160102
or as a command:

docmd.RunSQL "update tblPasswords set Password = " & Forms!frmPersPassChange![Text2] &
"where Function = 'PersonnelPassword'"
0
 
LVL 16

Accepted Solution

by:
Nestorio earned 150 total points
ID: 12160124
I forgot the single quotes again:

docmd.RunSQL "update tblPasswords set Password = '" & Forms!frmPersPassChange![Text2] &
"' where Function = 'PersonnelPassword'"
0
 

Author Comment

by:LoveToSpod
ID: 12160157
I am a very happy person, this can be applied into so much of my work. Thanks for the time you have given.

Much appreciated:

LoveToSpod
0
 

Author Comment

by:LoveToSpod
ID: 12160191
I don't know why Shane didn't get the Accepted answer on:

Assisted Answer from shanesuebsahakarn  feedback
Date: 09/27/2004 01:17PM GMT
Grade: A
 Assisted Answer  


Oh, I see. Then you need this:

DLookup("Password", "tblpasswords", "Function='PersonnelPassword'")

Should have been the above, oh well. You got the points that matters!!!

Thanks.
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12160192
Glad to have helped.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question