Solved

Dlookup problem

Posted on 2004-09-27
18
651 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now