Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 17 votes, 5.00 average. Display Modes
Old 05-29-2008, 03:07 AM   #1
Renoir
Registered User
 
Join Date: May 2007
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Renoir is on a distinguished road
DLOOKUP error 2471

I have a login form with a button on_click event where it looks up a password in a table.
If Me.txtPassword.Value = DLookup("Password", "Staff", "[LoginID]=" & Me.txtUser.Value) Then
txtPassword = password text box
txtUser = user ID ext box
Staff = table with the the following fields:
Password = the password
LoginID = the login name
However, I get a Run time error 2471
The expression you entered as a query parameter produced this error:
The error is whatever is typed in the txtUser text box.
Any hints anyone - I like to understand the DLOOKUP a bit better.
Thanks in advance.
Renoir

Renoir is offline   Reply With Quote
Old 05-29-2008, 03:09 AM   #2
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,306
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
I think your dlookup should look something like:

DLookup("[Password[", "Staff", "[LoginID]='" & Me.txtUser & "'")

???
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 05-29-2008, 03:10 AM   #3
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,306
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
Sorry:

DLookup("[Password]", "Staff", "[LoginID]='" & Me.txtUser & "'")

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 05-29-2008, 04:05 AM   #4
Renoir
Registered User
 
Join Date: May 2007
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Renoir is on a distinguished road
Works like a wild beast.

Could I use DLOOKUP for different access levels if I add this to my Staff table?
If so, can I use different levels such as edit - write - delete or does Access (2007) offer a more direct solution for this?

Cheers,

Renoir
Renoir is offline   Reply With Quote
Old 05-29-2008, 04:12 AM   #5
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,306
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
It looks like you have built your own security as opposed to using MS Access's built in security in which case you'll have to build the access level stuff from scratch.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 05-29-2008, 08:24 AM   #6
Renoir
Registered User
 
Join Date: May 2007
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Renoir is on a distinguished road
I was under the understanding that 2007 did't support this any more?
Renoir is offline   Reply With Quote
Old 05-29-2008, 08:28 AM   #7
KenHigg
Registered User
 
Join Date: Jun 2004
Posts: 13,306
Thanks: 8
Thanked 155 Times in 129 Posts
KenHigg has a spectacular aura about KenHigg has a spectacular aura about
Sorry, I haven't used 2007 -

I think Bob Larson has, maybe he knows...

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

ken

“You don’t learn to walk by following rules. You learn by doing, and by falling over.”
KenHigg is offline   Reply With Quote
Old 05-29-2008, 09:18 AM   #8
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,810 Times in 1,573 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Quote:
Originally Posted by Renoir View Post
Works like a wild beast.

Could I use DLOOKUP for different access levels if I add this to my Staff table?
If so, can I use different levels such as edit - write - delete or does Access (2007) offer a more direct solution for this?

Cheers,

Renoir
Yes, you can do that. I'm doing exactly that same thing for a current project. I have a hidden text box that I load when the program loads and I assign a text box on it the value of the access level, based on the user who logged in, so that I don't have to make function calls all of the time.

I can then just refer to that text box when I want to check the current login Access level.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 05-29-2008, 09:26 AM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,841
Thanks: 9
Thanked 3,824 Times in 3,767 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Quote:
Originally Posted by Renoir View Post
I was under the understanding that 2007 did't support this any more?
It is not supported in the new accdb format, but it still is in the mdb format:

http://office.microsoft.com/en-us/ac...345611033.aspx
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 05-29-2008, 11:16 AM   #10
vinzz
Newly Registered User
 
Join Date: Apr 2008
Posts: 47
Thanks: 0
Thanked 2 Times in 2 Posts
vinzz is on a distinguished road
Quote:
Originally Posted by boblarson View Post
Yes, you can do that. I'm doing exactly that same thing for a current project. I have a hidden text box that I load when the program loads and I assign a text box on it the value of the access level, based on the user who logged in, so that I don't have to make function calls all of the time.

I can then just refer to that text box when I want to check the current login Access level.
I'm using the same method, very simpel, very fast and the box is hidden :P
just referring to the .column() syntax.

ps: Dlookup is slowly, use Elookup module (search net) or open an sql instead :P
vinzz is offline   Reply With Quote
Old 05-31-2008, 05:39 AM   #11
Renoir
Registered User
 
Join Date: May 2007
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Renoir is on a distinguished road
Cheers all,

I had a look at Bob's example and have some questions about Forms!frmUSL.txtUN = strUser & Forms!frmUSL.txtUSL = intUSL
and how this "select case" works.
Could you post some comments on this?

The on_click event is copied below.

Private Sub cmdOK_Click()
Dim strUser As String
Dim strPWD As String
Dim intUSL As Integer
strUser = Me.txtUser
If DCount("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'") > 0 Then
strPWD = DLookup("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'")
If strPWD = Me.txtPWD Then
intUSL = DLookup("[SecurityGroup]", "tblUsers", "[UserName]='" & Me.txtUser & "'")
Forms!frmUSL.txtUN = strUser
Forms!frmUSL.txtUSL = intUSL
Select Case intUSL
Case 1
DoCmd.OpenForm "frmTestData", acNormal
Case 2
DoCmd.OpenForm "frmTestData", acNormal, , , acFormReadOnly
Case 3
MsgBox "Not configured yet", vbExclamation, "Not configured"
Case 4
MsgBox "Not configured yet", vbExclamation, "Not configured"
End Select
DoCmd.Close acForm, "frmTestLogin", acSaveNo
End If
End If
Renoir is offline   Reply With Quote
Old 05-31-2008, 08:49 AM   #12
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,810 Times in 1,573 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Quote:
Originally Posted by Renoir View Post
Cheers all,

I had a look at Bob's example and have some questions about Forms!frmUSL.txtUN = strUser & Forms!frmUSL.txtUSL = intUSL
and how this "select case" works.
Could you post some comments on this?

The on_click event is copied below.

Private Sub cmdOK_Click()
Dim strUser As String
Dim strPWD As String
Dim intUSL As Integer
strUser = Me.txtUser
If DCount("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'") > 0 Then
strPWD = DLookup("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'")
If strPWD = Me.txtPWD Then
intUSL = DLookup("[SecurityGroup]", "tblUsers", "[UserName]='" & Me.txtUser & "'")
Forms!frmUSL.txtUN = strUser
Forms!frmUSL.txtUSL = intUSL
Select Case intUSL
Case 1
DoCmd.OpenForm "frmTestData", acNormal
Case 2
DoCmd.OpenForm "frmTestData", acNormal, , , acFormReadOnly
Case 3
MsgBox "Not configured yet", vbExclamation, "Not configured"
Case 4
MsgBox "Not configured yet", vbExclamation, "Not configured"
End Select
DoCmd.Close acForm, "frmTestLogin", acSaveNo
End If
End If
First, intUSL (integer User Security Level) is assigned the value from the table - which security level does the user have.

The select case statement looks at the security level returned and if it is a 1 it opens the form normally and if it is a 2 (read-only security level) it opens the form in read only. The security levels are arbitrary for that sample and could be anything you want.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 10-11-2009, 12:49 PM   #13
tiredcoder
Registered User
 
Join Date: Oct 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
tiredcoder is on a distinguished road
Re: DLOOKUP error 2471

Hey.....
I'm pretty much stuck in a rut for an assignment here. Could really use the assistance. Pretty much i wanna get a particular permission set to the user "Clerk" that when the main menu comes up, two buttons are disabled preventing them from writing to the DB. so this is wat i have thus far:

Dim intAuth As Integer
intAuth = DLookup("UserID", "Users", "[UserName]='" & Me.cbousername & "'")

If intAuth = 4 Then
Me.AddForm.Enabled = False
Me.EditRecords.Enabled = False
Else
AddForm.Enabled = True
PQueries.Enabled = True
Tile_Price_Check.Enabled = True
Supplier_Items.Enabled = True
Report.Enabled = True
View_Forms.Enabled = True
EditRecords.Enabled = True
End If
I'm either getting error 2741 due to the DLookup, or my criteria not being read. some assistance would be greatly appreciated. Much thnx.
tiredcoder is offline   Reply With Quote
Old 10-12-2009, 03:46 AM   #14
vinzz
Newly Registered User
 
Join Date: Apr 2008
Posts: 47
Thanks: 0
Thanked 2 Times in 2 Posts
vinzz is on a distinguished road
Re: DLOOKUP error 2471

Quote:
Originally Posted by tiredcoder View Post
...
Dim intAuth As Integer
intAuth = DLookup("UserID", "Users", "[UserName]='" & Me.cbousername & "'")
....
So you're trying to get some information from the table "users" and you only get the value from the column "UserID" wich should be numeric (long). You filter this with the colum Username (wich is a string) and check it with a combobox 'cboUsername' on your form. What is the referenced table/query/list that you use in this combobox? is the reference column a string? Most of the time it is numeric. If so, search the right column of the combobox and use that instead, for example:

information in the combo: Userid;username. first column (0) is the unique field <-- the first column is a numeric field and in the second a string (the name itself)

if you use it like this you don't need a dlookup function. just call me.cbousername (numeric) if not use the function like this:

Code:
Dim intAuth As long
intAuth = DLookup("UserID", "Users", "[UserName]='" & Me.cbousername.column(1) & "'")
.column(1) references to the second field in the combo.
vinzz is offline   Reply With Quote
Old 10-16-2009, 01:17 PM   #15
tiredcoder
Registered User
 
Join Date: Oct 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
tiredcoder is on a distinguished road
Re: DLOOKUP error 2471

ohk, sweet. thanks. i eventually did something similar to that to get it to work.
Definitely removed the DLookup

If UserID = "4" Then
Me.AddForm.Enabled = False
Me.EditRecords.Enabled = False
Me.PQueries.Enabled = True
Me.Tile_Price_Check.Enabled = True
Me.Supplier_Items.Enabled = True
Me.Report.Enabled = True
Me.View_Forms.Enabled = True
Else
AddForm.Enabled = True
PQueries.Enabled = True
Tile_Price_Check.Enabled = True
Supplier_Items.Enabled = True
Report.Enabled = True
View_Forms.Enabled = True
EditRecords.Enabled = True
End If


Much thnx though vinnz. would give u a hug but...... yea.

tiredcoder is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
dlookup or query? Listerino Queries 4 10-11-2007 07:13 AM
A lot going on and I'm lost.......DLookUp and Validate field entries BrokenBiker Modules & VBA 16 05-06-2006 05:36 PM
Dlookup in a remote query DataMiner Modules & VBA 4 02-02-2006 04:16 PM
Run Macro if Dlookup is Null? davin2929 Forms 7 10-27-2005 07:24 AM
Dlookup KenHigg Modules & VBA 1 06-16-2005 05:37 AM




All times are GMT -8. The time now is 07:21 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World