asked on

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

peter57r

Hello LoveToSpod,

Change the double quotes around PersonnelPassword to single quotes.

Pete

LoveToSpod

ASKER

Can you edit, and show me:

Cherrs

shanesuebsahakarn

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)

Nestorio

Try this:

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

LoveToSpod

ASKER

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

SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nestorio

If I understand it would be:

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

LoveToSpod

ASKER

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

Nestorio

Sorry, I've forgot the single quotes surrounding the literal 'PersonnelPassword'.

SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LoveToSpod

ASKER

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

LoveToSpod

ASKER

Shane, Thanks for the above info on quotes " ' & etc...

I will paste that into my reference files!!

Really appreciated.

Nestorio

Try this,

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

Nestorio

or as a command:

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

ASKER CERTIFIED SOLUTION

Nestorio

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LoveToSpod

ASKER

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

LoveToSpod

ASKER

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.

Nestorio

Glad to have helped.