DLOOKUP error 2471 (1 Viewer)

Renoir

Registered User.
Local time
Today, 12:50
Joined
May 31, 2007
Messages
42
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
 

KenHigg

Registered User
Local time
Today, 04:20
Joined
Jun 9, 2004
Messages
13,327
I think your dlookup should look something like:

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

???
 

KenHigg

Registered User
Local time
Today, 04:20
Joined
Jun 9, 2004
Messages
13,327
Sorry:

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

Renoir

Registered User.
Local time
Today, 12:50
Joined
May 31, 2007
Messages
42
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
 

KenHigg

Registered User
Local time
Today, 04:20
Joined
Jun 9, 2004
Messages
13,327
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.
 

Renoir

Registered User.
Local time
Today, 12:50
Joined
May 31, 2007
Messages
42
I was under the understanding that 2007 did't support this any more?
 

KenHigg

Registered User
Local time
Today, 04:20
Joined
Jun 9, 2004
Messages
13,327
Sorry, I haven't used 2007 - :)

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

boblarson

Smeghead
Local time
Today, 01:20
Joined
Jan 12, 2001
Messages
32,059
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.
 

vinzz

Registered User.
Local time
Today, 01:20
Joined
Apr 24, 2008
Messages
47
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
 

Renoir

Registered User.
Local time
Today, 12:50
Joined
May 31, 2007
Messages
42
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
 

boblarson

Smeghead
Local time
Today, 01:20
Joined
Jan 12, 2001
Messages
32,059
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.
 

tiredcoder

New member
Local time
Today, 01:20
Joined
Oct 11, 2009
Messages
2
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. :confused: some assistance would be greatly appreciated. Much thnx.:)
 

vinzz

Registered User.
Local time
Today, 01:20
Joined
Apr 24, 2008
Messages
47
...
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.
 

tiredcoder

New member
Local time
Today, 01:20
Joined
Oct 11, 2009
Messages
2
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.
 

ss6857

Registered User.
Local time
Today, 03:20
Joined
Jul 12, 2011
Messages
38
Hi everyone, I'm also doing a Dlookup in vba and I was getting this same error. However, the criteria has a hyphen in the name. For example if I was looking up ColumnName = "toy-r-us", it is giving me The expression you entered as a query parameter produced this error: 'toys'. Here is my code:

Dim Cname As String
Dim Cnum As Integer

Me.Combo113.SetFocus
Cname = Me.Combo113.Text

Cnum = DLookup("[P_ID]", "Price_List", "[Description] = " & Cname & "")

any suggestions? I really don't want to get rid of my hyphen if I don't have to
 

ss6857

Registered User.
Local time
Today, 03:20
Joined
Jul 12, 2011
Messages
38
Right after I posted it I figured it out.. I was missing the single quotes. But thank you!
 

taloker

Registered User.
Local time
Today, 01:20
Joined
Mar 27, 2012
Messages
13
I am facing a problem
2 tables LU Product and Sales Point
I want the price from LU product populates when I enter the product in Sales point form
The code I have written is as below
Private Sub Price_AfterUpdate() Product = DLookup("Product", "LU Product", "Price=" & Price) End Sub It works when I enter the price and the product names populates BUT

gives error when applied gives error 2471
Private Sub combo10_AfterUpdate()
Price = DLookup("Price", "LU Product", "Product=" & Combo10)
End Sub

The combo10 is Product field in Form
 

Users who are viewing this thread

Top Bottom