Can't get Dlookup to work (1 Viewer)

[Chuckieinoz]

Hey people attatched is a test Db where i can't seem to get the Profile Id to work in the MainDashboard.
The Code is in the Onclick of Login Button

 

[GaP42]

Hey @Chuckieinoz welcome to the forum!
1. Set Option Explicit after option Compare Database - it will help with your debugging - in every module (can be set as a default). after setting it - try compiling and you will see:
2. You have not Dim'd the variable uFound or aProfile:
Dim Ufound As Integer
Dim aprofile As Integer
When uFound is defined, the first DCount - for ufound works
3 For aProfile: you need to remove exta spaces :
aprofile = DLookup("prfID", "tbl_user", "uUsername = '" & Me.txt_username & "'")
See if that gets you where you need to be.

 

[Gasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.

Example:

tt="Eg'g"

? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

 

[MajP]

My guess is you want it to be case sensitive. This is not. You can try setting the form to Option Binary, but I usually build a case sensitive function

Public Function UserFound(UserName As String, Password As String) As Boolean
  Dim rs As DAO.Recordset
  Dim criteria As String
  Dim Msg As String
  
  criteria = "uUserName = '" & UserName & "' AND uPassword = '" & Password & "' AND uStatus = 'Active'"
  Set rs = CurrentDb.OpenRecordset("select * from tbl_User where " & criteria)
  UserFound = True
  If rs.EOF Then
    UserFound = False
  Else
    If StrComp(UserName, rs!uUserName, vbBinaryCompare) <> 0 Then UserFound = False
    If StrComp(Password, rs!uPassword, vbBinaryCompare) <> 0 Then UserFound = False
  End If
  
End Function

 

[Chuckieinoz]

My guess is you want it to be case sensitive. This is not. You can try setting the form to Option Binary, but I usually build a case sensitive function

Public Function UserFound(UserName As String, Password As String) As Boolean
  Dim rs As DAO.Recordset
  Dim criteria As String
  Dim Msg As String
 
  criteria = "uUserName = '" & UserName & "' AND uPassword = '" & Password & "' AND uStatus = 'Active'"
  Set rs = CurrentDb.OpenRecordset("select * from tbl_User where " & criteria)
  UserFound = True
  If rs.EOF Then
    UserFound = False
  Else
    If StrComp(UserName, rs!uUserName, vbBinaryCompare) <> 0 Then UserFound = False
    If StrComp(Password, rs!uPassword, vbBinaryCompare) <> 0 Then UserFound = False
  End If
 
End Function

Hey @Chuckieinoz welcome to the forum!
1. Set Option Explicit after option Compare Database - it will help with your debugging - in every module (can be set as a default). after setting it - try compiling and you will see:
2. You have not Dim'd the variable uFound or aProfile:
Dim Ufound As Integer
Dim aprofile As Integer
When uFound is defined, the first DCount - for ufound works
3 For aProfile: you need to remove exta spaces :
aprofile = DLookup("prfID", "tbl_user", "uUsername = '" & Me.txt_username & "'")
See if that gets you where you need to be.

Thanks mate it worked perfectly with spaces still in Interestingly it threw an invalid use of Null I love the Option excplicit Thankyou