UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Tempvars Dlookup Unable To Locate Value, Access 2016    
 
   
Zaddicus
post Apr 12 2019, 08:37 AM
Post#1



Posts: 56
Joined: 3-April 19
From: Cardiff


Hi again,

Odd one this time. I've written the code below to check username/password and log users in (all good) one thing I added to the code was a tempvar to return the persons 'username' this worked perfectly for a while. HOWEVER...

I've changed all my tables/relationships off the advice of the forums and now the tempvar should return the 'userID'. This tempvar is used to set the userID on various data entry forms automatically and also some cosmetic things like displaying their name on the home form.

Problem is I get the error in the screenshot attached.

CODE
Private Sub Command1_Click()

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  Dim LoggedUserID As TempVars

  If Trim(Me.txt_UserName.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, Title:="Username Required"
    Me.txt_UserName.SetFocus
    Exit Sub
  End If

  If Trim(Me.txt_Password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, Title:="Password Required"
    Me.txt_Password.SetFocus
    Exit Sub
  End If

  'query to check if login details are correct
  strSQL = "SELECT FirstName FROM tbl_Users WHERE UserName = """ & Me.txt_UserName.Value & """ AND Password = """ & Me.txt_Password.Value & """"

  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  If rst.EOF Then
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, Title:="Login Error"
    Me.txt_UserName.SetFocus
  Else
    MsgBox prompt:="Welcome to E-Link, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, Title:="Login Successful"
    TempVars!LoggedUserID = DLookup([UserId], [tbl_Users], [UserName] = Me.txt_UserName.Value)
    DoCmd.Close acForm, "adf_login", acSaveYes
    DoCmd.OpenForm "usf_home"
  End If

Set db = Nothing
Set rst = Nothing

End Sub


The error is in the line:
CODE
    TempVars!LoggedUserID = DLookup([UserId], [tbl_Users], [UserName] = Me.txt_UserName.Value)


However it looks alright to me...

Thanks in advance,
Cas
Attached File(s)
Attached File  snip027.PNG ( 4.27K )Number of downloads: 0
 

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
GroverParkGeorge
post Apr 12 2019, 08:57 AM
Post#2


UA Admin
Posts: 34,838
Joined: 20-June 02
From: Newcastle, WA


In your DLookup, you need quotes " around the names of the field, the table and the parameter argument.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Zaddicus
post Apr 12 2019, 10:21 AM
Post#3



Posts: 56
Joined: 3-April 19
From: Cardiff


Well, that explains why I was getting the error...

Thanks for the quick fix smile.gif

Related issue, one of my unbound user controls has control source expression

CODE
=DLookUp("[FirstName]","[tbl_Users]","[UserID]='" & [TempVars]![LoggedUserID].[Value] & "'")


However this is returning #Error

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
GroverParkGeorge
post Apr 12 2019, 10:31 AM
Post#4


UA Admin
Posts: 34,838
Joined: 20-June 02
From: Newcastle, WA


I don't see a syntax problem there, unless it is because you've used the string delimiter around the UserID, which may be a number, not a string.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Zaddicus
post Apr 15 2019, 02:36 AM
Post#5



Posts: 56
Joined: 3-April 19
From: Cardiff


Morning,

I managed to track the issue down to a very odd issue, the tempvar is actually returning a null value.

CODE
Private Sub Command1_Click()

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  Dim AUID As TempVars

  If Trim(Me.txt_UserName.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, Title:="Username Required"
    Me.txt_UserName.SetFocus
    Exit Sub
  End If

  If Trim(Me.txt_Password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, Title:="Password Required"
    Me.txt_Password.SetFocus
    Exit Sub
  End If

  'query to check if login details are correct
  strSQL = "SELECT FirstName FROM tbl_Users WHERE UserName = """ & Me.txt_UserName.Value & """ AND Password = """ & Me.txt_Password.Value & """"

  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  If rst.EOF Then
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, Title:="Login Error"
    Me.txt_UserName.SetFocus
  Else
    TempVars!AUID = DLookup("[UserId]", "[tbl_Users]", "[UserName]" = "Me.txt_UserName.Value")
    MsgBox prompt:="Welcome to E-Link, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, Title:="Login Successful"
    DoCmd.Close acForm, "adf_login", acSaveYes
    DoCmd.OpenForm "usf_home"
  End If

Set db = Nothing
Set rst = Nothing

End Sub


The above is a slightly amended code, previously when I set the tempvar to me.txt_UserName.Value it would work and return the correct value (for example it would return 'dbAdmin'). Now that I've altered it in attempt to return the user ID (in this case '1') it no longer works.

This is why half of my database has stopped working because a lot of my codes require the AUID to lookup the user's "Security Level"

Really irking me that I haven't found a fix to this over the weekend too...

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
PaulBrand
post Apr 15 2019, 07:24 AM
Post#6



Posts: 1,695
Joined: 4-September 02
From: Oxford UK


Your criteria in Dlookup is wrongly syntaxed...

CODE
=DLookUp("[UserId]", "[tbl_Users]", "[UserName]='" & Me.txt_UserName & "'")

This post has been edited by PaulBrand: Apr 15 2019, 07:27 AM

--------------------
Paul
Go to the top of the page
 
Zaddicus
post Apr 15 2019, 07:40 AM
Post#7



Posts: 56
Joined: 3-April 19
From: Cardiff


Thanks Paul,

I did notice that shortly after however there appears to be no 'edit' button on this thread.

Out of curiosity, when returning a tempvar that is linked to a combobox field is it possible to return the 2nd column value.

Example, one tempvar I set is 'AUSL' (Active User Security Level) and this returns as a numerical value. The Value being the PK for tbl_UserType, however I was wondering if it is possible to get the control field to display the text value (1 = Admin, 2 = basic, 3 = Tier 1 etc...)

This is more of a cosmetic thing than anything (Trying to avoid confusing the non-tech types when they see their access/security level as a number)

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2019, 08:05 AM
Post#8


UA Admin
Posts: 34,838
Joined: 20-June 02
From: Newcastle, WA


Combo and List Box columns are indexed, left to right, starting with 0. The first column, therefore is 0. That is usually also the bound column; i.e. the one to which a field in the current form's recordset is bound.

The second column is column 1, and so on.

You can reference any valid column in a Combo or List Box by using this syntax. Me.cboYourComboBoxNameGoesHere.Column(0), Me.cboYourComboBoxNameGoesHere.Column(1), etc. Obviously, the actual number of columns set up determines how many index references would be valid.
This post has been edited by GroverParkGeorge: Apr 15 2019, 08:10 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th April 2019 - 02:56 PM