Dlookup problem

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
LoveToSpodAsked:
Who is Participating?
 
NestorioConnect With a Mentor Commented:
I forgot the single quotes again:

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

Change the double quotes around PersonnelPassword to single quotes.

Pete
0
 
LoveToSpodAuthor Commented:
Can you edit, and show me:

Cherrs
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
shanesuebsahakarnCommented:
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
 
NestorioCommented:
Try this:

StrPersPass = DLookup("Password", "tblpasswords", "Function = '" &  PersonnelPassword & "'")
0
 
LoveToSpodAuthor Commented:
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
 
shanesuebsahakarnConnect With a Mentor Commented:
Oh, I see. Then you need this:

DLookup("Password", "tblpasswords", "Function='PersonnelPassword'")
0
 
NestorioCommented:
If I understand it would be:

StrPersPass = DLookup("Password", "tblpasswords", "Function = PersonnelPassword")
0
 
LoveToSpodAuthor Commented:
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
 
NestorioCommented:
Sorry, I've forgot the single quotes surrounding the literal 'PersonnelPassword'.
0
 
shanesuebsahakarnConnect With a Mentor Commented:
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
 
LoveToSpodAuthor Commented:
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
 
LoveToSpodAuthor Commented:
Shane, Thanks for the above info on quotes " ' & etc...

I will paste that into my reference files!!

Really appreciated.
0
 
NestorioCommented:
Try this,

update tblPasswords set Password = Forms!frmPersPassChange![Text2]
where Function = "PersonnelPassword"
0
 
NestorioCommented:
or as a command:

docmd.RunSQL "update tblPasswords set Password = " & Forms!frmPersPassChange![Text2] &
"where Function = 'PersonnelPassword'"
0
 
LoveToSpodAuthor Commented:
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
 
LoveToSpodAuthor Commented:
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
 
NestorioCommented:
Glad to have helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.