Solved

Dlookup problem

Posted on 2004-09-27
18
652 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
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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
data analyst 3 49
Storing Combo Box Selection in Table 12 42
Web based Access Database and licencing 5 29
Importing and Dropping Table in Access 11 24
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
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…

911 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

20 Experts available now in Live!

Get 1:1 Help Now