Dlookup giving an error when it can't find a logged in user in a table

[Ron]

I have this Dlookup routine attached to the OnLoad
function of a Form.
The User Level of the current user is checked against a
table at the time they login, this person should exist in
one of 2 tables.
Their assigned UserLevel# determines what controls they
see on the Startup form.
The code works fine if the user belongs to one of the
tables, but it errors out when it can't find a user on one
of the lists. I figured the "ElseIf" statement with the ""
value and the proceeding "MsgBox" function would have
helped to take care of that, but it doesn't.
I can't seem to figure out what is exactly wrong. I've
tried different If..Then scenarios and nothing seems to
work properly.
Any help would be appreciated.

Thanks.

Ron (See routine below)
---------------------------------------------------------

Private Sub Form_Load()

Dim strUserLevel As String, strUser As String

strUser = CurrentUser()
strUserLevel = Nz(DLookup
("pUserLevel", "[tblProjectMgrs]", _
"ProjectName=""" & strUser & """"), "")

With Me
If strUserLevel = "" Then
strUserLevel = Nz(DLookup"
tUserLevel", "[refTMSStrategyMgr]", _
"StrategyMgr=""" & strUser & """"), "")
If strUserLevel = 2 Then
.cmdOpenDMJIform.Visible = False
.DMJobTrackinglabel.Visible = False

ElseIf strUserLevel = "" Then
MsgBox "User" & CurrentUser() & "is
not in either table." & _
"Please contact the system Admin to
add you to the appropriate table.", vbOKOnly
.cmdOpenIBTform.Visible = False
.cmdOpenScriptTrackform.Visible = False
.IBtrackinglabel.Visible = False
.ScriptTrackinglabel.Visible = False
.cmdOpenDMJIform.Visible = False
.DMJobTrackinglabel.Visible = False
End If
Else
If strUserLevel = 1 Then
.cmdOpenIBTform.Visible = False
.cmdOpenScriptTrackform.Visible = False
.IBtrackinglabel.Visible = False
.ScriptTrackinglabel.Visible = False
End If
End If
End With


End Sub

 

[Anand]

Hi Ron
I have a similar system working. I use the Recordset method to find if a user exists and if he/she does then to determine the user level.

You could use .RecordCount to check if a user exists. Your code could look something like thi
Dim dbs as DAO.Databas
Dim rst as DAO.RecordSe
Dim stSQL as strin

dbs = CurrentD
rst = dbs.OpenRecordset("stSQL"
stSQL = "SELECT EmployeeID, UserName, UserLevel# FROM tblUserDetails WHERE tblUserDetails.UserName = CurrentUser(
With rs
If .RecordCount = 0 then 'Such a user does not exis
Msgbox "Wrong User
End i
end wit

I am assuming
You have enabled Access Securit
You are using DA
You have entered the UserName of Access Security in the same table as your UserLevel# (This is not an elegant solution but dit works for me)

Hope this helps
Anand

 

[Ron]

Anand,

That's a nice little code but I'm having a little trouble
getting it to fit for what I'm trying to do.
Same principle though.
My code does work up to the point when it can't find a
user (that shouldn't happen, but it could), then it stops
after that because of a Type Mismatch error. That's where
I'm hitting a wall in trying to figure out how to make it
deal with a non-existant user. It's probably something
really simple, but I'm just not grasping it.
Any suggestions from you or anyone else scanning this
thread would be greatly appreciated.

Thanks!

Ron

-----Original Message-----
Hi Ron,
I have a similar system working. I use the Recordset

method to find if a user exists and if he/she does then to
determine the user level.

You could use .RecordCount to check if a user exists.

Your code could look something like this

Dim dbs as DAO.Database
Dim rst as DAO.RecordSet
Dim stSQL as string

dbs = CurrentDb
rst = dbs.OpenRecordset("stSQL")
stSQL = "SELECT EmployeeID, UserName, UserLevel# FROM

tblUserDetails WHERE tblUserDetails.UserName = CurrentUser
()

With rst
If .RecordCount = 0 then 'Such a user does not exist
Msgbox "Wrong User"
End if
end with

I am assuming:
You have enabled Access Security
You are using DAO
You have entered the UserName of Access Security in the

same table as your UserLevel# (This is not an elegant
solution but dit works for me).

 

[Anand]

Ron
Here's exactly how I do it. There's other stuff in the function. Just ignore whatever you dont want. About the Type Mismatch, one of the fields in your table could be having a number type field while you are trying to assign a text field to it. Just recheck. Hope this helps

HT
Anan

Function fnSecCatID() As Intege
On Error GoTo ErrorHandle

Dim dbs As DAO.Databas
Dim rstEmployees As DAO.Recordse
Dim stSQL As Strin
Dim hostCompName$, stMsgInf, AppName$, hmo

stSQL = "SELECT EmployeeID, UserName, SecurityCatID "
& "FROM tblEmployeeDetails "
& "WHERE tblEmployeeDetails.UserName = CurrentUser()

Set dbs = CurrentD
Set rstEmployees = dbs.OpenRecordset(stSQL

With rstEmployee
If .RecordCount > 0 The
fnSecCatID = !SecurityCatID 'Check the Security Cat ID of the Current Use
ElseIf .RecordCount = 0 The
MsgBox "" & CurrentUser & ", you are not a registered user. "
& "You will not be allowed to perform many tasks in Payroll." & Chr(10) & "Please contact the Admin department",
vbCritical + vbOKOnl
fnSecCatID =
End I
End Wit

If CurrentUser = "Admin" The
hostCompName$ = "Abhatta
stMsgInf = "System Alert: 'Admin' Login detected
AppName$ = "Net Send " & hostCompName$ & " " & stMsgInf & "
hmod = Shell(AppName$, vbHide
End I

Set dbs = Nothin
rstEmployees.Clos

Exit_Function
Exit Functio

ErrorHandler
MsgBox Err.Description & ": " & Err.Numbe
Resume Exit_Functio

End Functio