Solved

Dlookup problem

Posted on 2004-09-27
18
653 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

776 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