Getting a Type Mismatch error with this Dlookup function on a form

R

Ron

I have this Dlookup routine attached to the OnLoad
function of a Form.
It looks up the User Level of the current user
logged in at the time, who should exist in one of 2 tables.
Depending on their assigned UserLevel# determines what
controls they see on the Startup form.
The code works perfectly if the user belongs to one of the
tables, but it gives a "Type Mismatch" error if it can't
find a logged in user. The "ElseIf" statement with
a "MsgBox" function I thought would have helped to take
care of that, but it doesn't.
Can anyone see, judging by the code, why a "Type Mismatch"
would occur. I can't seem to figure it out.
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
 
K

Ken Snell

If I understand correctly, you're saying the code is giving the mismatch
error if it cannot find the user in the table. You don't state specifically
on which code step you're getting the error, so I'm not sure.

But are you sure that you're getting a value from the CurrentUser function?
If that is returning a Null, then you'll get the mismatch on the first
DLookup when your code tries to insert Null as a string value.


Ron said:
I have this Dlookup routine attached to the OnLoad
function of a Form.
It looks up the User Level of the current user
logged in at the time, who should exist in one of 2 tables.
Depending on their assigned UserLevel# determines what
controls they see on the Startup form.
The code works perfectly if the user belongs to one of the
tables, but it gives a "Type Mismatch" error if it can't
find a logged in user. The "ElseIf" statement with
Click to expand...
--
Ken Snell
a "MsgBox" function I thought would have helped to take
care of that, but it doesn't.
Can anyone see, judging by the code, why a "Type Mismatch"
would occur. I can't seem to figure it out.
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
Click to expand...
 
V

Van T. Dinh

Have you checked whether you used the correct Field name?

"ProjectName" in the DLookUp looks a bit suspicious to me from the naming
system you used.
 
D

Douglas J. Steele

Or perhaps ProjectName is a lookup field, in which case what's being stored
there is the ID, not the text that appears when you look at the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Van T. Dinh said:
Have you checked whether you used the correct Field name?

"ProjectName" in the DLookUp looks a bit suspicious to me from the naming
system you used.

--
HTH
Van T. Dinh
MVP (Access)


Ron said:
I have this Dlookup routine attached to the OnLoad
function of a Form.
It looks up the User Level of the current user
logged in at the time, who should exist in one of 2 tables.
Depending on their assigned UserLevel# determines what
controls they see on the Startup form.
The code works perfectly if the user belongs to one of the
tables, but it gives a "Type Mismatch" error if it can't
find a logged in user. The "ElseIf" statement with
a "MsgBox" function I thought would have helped to take
care of that, but it doesn't.
Can anyone see, judging by the code, why a "Type Mismatch"
would occur. I can't seem to figure it out.
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
Click to expand...
Click to expand...
 
V

Van T. Dinh

Hi Doug

I was looking at Ron's other Table and he used "StrategyMgr" for the
correspoding Field. Thus, I expected that he would use "ProjectMgr" rather
than "ProjectName". Hence, my guess was that "ProjectName" may be the wrong
Field (name) to use in the DLookUp.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dlookup giving an error when it can't find a logged in user in a table 3
Dlookup function getting a Type Mismatch error 2
How to do a second DLookup inside same sub 6
Type MisMatch Error Associated With Dlookup Function On Form 2
Running function which gets a form parameter type + Reseting existing field's parameter to null or " 1
calling a Function from strSQL getting type mismatch error 2

Top