Solved: Dlookup problem

[Ryanr]

Hello there!
I am currently trying to create a simple login screen for my database. I have a table with the login details - Username, password and access level - and it works fine, except DLookup isn't working for my passwords. It simply can't find anything. When I take out the password from the login form, both the username and the access level sections work as they should

'Check username'
Check = DCount("[Username]", "Tbl_Login", "Username = Forms!Login!Text3")
If Check = 1 Then
checklogin = 1
Else
checklogin = 0
MsgBox ("NAMEWRONG")
End If

'check password'
If checklogin = 1 Then PWcheck = DCount("[Password]", "Tbl_Login", "username = Forms!Login!Text3")
If UCase(PWcheck) = UCase(Forms!Login!Text5) Then
checklogin = 2
Else
checklogin = 0
MsgBox ("PASSWRONG")
End If
If checklogin = 0 Then
MsgBox ("Login failed. If problems persist, contact IT helpdesk")
Exit Sub
End If

'***Check accesslevel***''
If checklogin = 2 Then accesslevel = DCount("[Access level]", "Tbl_Login", "Username=Forms!Login!Text3")
If accesslevel = 0 Then
DoCmd.Close
DoCmd.OpenForm ("OPENING_FORM")
Form_OPENING_FORM.Command1.Visible = False
End If
If accesslevel = 1 Then
DoCmd.Close
DoCmd.OpenForm ("OPENING_FORM")
End If
End Sub


Please can anyone shed any light on this?

[Imdabaum]

DLookup or DCount? I don't see Dlookup in your code.

If DCount is suppose to be DLookup, then try this.

DLookup("[Username]", "Tbl_Login", "Username = '" & Forms!Login!Text3 & "'") OR
DLookup("[Username]", "Tbl_Login", "Username = """ & chr(34) & Forms!Login!Text3 & """)

I was having a similar problem and the help description for Access 2007 DLookup tells us that your way of doing it should work. Experience says that it will not.

[Ryanr]

So thats why the password section wasn't working...
Lmao D:
Probably the dumbest question you will get for a while, and probably the easiest solution you will ever provide
Thank you :) I have learnt a valuable lesson today
ALWAYS CHECK YOUR CODE
:doh:

[geekgirlau]

I would suggest that as you are trying to obtain 3 separate pieces of information from the table, that you use a recordset instead.


Dim rec As DAO.Recordset
Dim strSQL As String
Dim strReason As String


On Error GoTo ErrHandler
strSQL = "SELECT Username, Password, [Access level] " & _
"FROM Tbl_Login " & _
"WHERE Username = """ & Forms!Login!Text3 & """"

Set rec = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

With rec
'Check username
If .EOF Then
strReason = "invalid user name"

Else
'check password
If UCase(!Password) <> UCase(Forms!Login!Text5) Then
strReason = "invalid password"
Else
DoCmd.Close
DoCmd.OpenForm ("OPENING_FORM")

If ![Access level] = 0 Then
Form_OPENING_FORM.Command1.Visible = False
End If
End If
End If
End With
' error message if logon failed
If strReason <> "" Then
MsgBox ("Login failed - " & strReason & vbCrLf & _
"If problems persist, contact IT helpdesk")
End If


ExitHere:
On Error Resume Next
rec.Close
Set rec = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbCritical, "Unexpected error (" & Err.Number & ")"
Resume ExitHere