How to do a second DLookup inside same sub

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

I have this Sub routine (see below) that's built into a
Form that basically looks up the value of a UserLevel in a
table which is based on the CurrentUser who's logged in at
that time. Then, certain controls on the form are hidden
based on the level of the user logged in.
My question is (and it's probably a simple answer): How
would I set up a second Lookup procedure inside that Sub
routine? The reason for the second one is that it needs
to reference another table to find a different set of
users and userlevels in order to hide other controls on
the form.
Thanks for the assistance on this.

Ron
-----------------------------------------------
Private Sub Form_Load() 'Current Sub

Dim sUserLevel As String

sUserLevel = DLookup
("pUserLevel", "[tblProjectMgrs]", "ProjectName=""" &
CurrentUser() & """")

If sUserLevel = 1 Then
Me.cmdOpenIBTform.Visible = False
Me.cmdOpenScriptTrackform.Visible = False
Me.IBtrackinglabel.Visible = False
Me.ScriptTrackinglabel.Visible = False

End If

End Sub
--------------------------------------
(This is the code for the 2nd Lookup but not sure how to
incorporate it into the Sub above.)

Dim rUserLevel As String

rUserLevel = DLookup
("tUserLevel", "[refTMSStrategyMgr]", "StrategyMgr=""" &
CurrentUser() & """")

If rUserLevel = 2 Then
Me.cmdOpenDMJIform.Visible = False
Me.DMJobTrackinglabel.Visible = False

End If
 
you're dimming a second variable to use with the second If statement, so
there's no possible conflict that i can see. just move the code into the
sub, below the end of the first If statement. it should run fine.

in fact, i would probably create a string variable to hold the value of
current user so the system doesn't have to look it up twice, and just reuse
the UserLevel variable; assuming you posted the entire sub, it only gets
used once in your current sub. so my code would look like:

Dim strUserLevel As String, strUser As String

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

With Me
If strUserLevel = 1 Then
.cmdOpenIBTform.Visible = False
.cmdOpenScriptTrackform.Visible = False
.IBtrackinglabel.Visible = False
.ScriptTrackinglabel.Visible = False
End If

strUserLevel = DLookup("tUserLevel", "[refTMSStrategyMgr]", _
"StrategyMgr=""" & strUser & """")

If strUserLevel = 2 Then
.cmdOpenDMJIform.Visible = False
.DMJobTrackinglabel.Visible = False
End If
End With

hth
 
Tina - that worked...kind of. I'm running into an Invalid
use of Null statement on either the first or second
Dlookup function (depending on what table the user belongs
to)when I open the form.
I think it's trying to use both routines at the same time
for each user. Maybe there should be another End
statement or an Exit Statement with the first routine just
so that if it finds a match with level one it should stop
and exit, but if no match then go to to next routine and
find it.
Not sure of the proper context to write it in.

Thanks,

RV


-----Original Message-----
you're dimming a second variable to use with the second If statement, so
there's no possible conflict that i can see. just move the code into the
sub, below the end of the first If statement. it should run fine.

in fact, i would probably create a string variable to hold the value of
current user so the system doesn't have to look it up twice, and just reuse
the UserLevel variable; assuming you posted the entire sub, it only gets
used once in your current sub. so my code would look like:

Dim strUserLevel As String, strUser As String

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

With Me
If strUserLevel = 1 Then
.cmdOpenIBTform.Visible = False
.cmdOpenScriptTrackform.Visible = False
.IBtrackinglabel.Visible = False
.ScriptTrackinglabel.Visible = False
End If

strUserLevel = DLookup
Click to expand...
("tUserLevel", "[refTMSStrategyMgr]", _
"StrategyMgr=""" & strUser & """")

If strUserLevel = 2 Then
.cmdOpenDMJIform.Visible = False
.DMJobTrackinglabel.Visible = False
End If
End With

hth


I have this Sub routine (see below) that's built into a
Form that basically looks up the value of a UserLevel in a
table which is based on the CurrentUser who's logged in at
that time. Then, certain controls on the form are hidden
based on the level of the user logged in.
My question is (and it's probably a simple answer): How
would I set up a second Lookup procedure inside that Sub
routine? The reason for the second one is that it needs
to reference another table to find a different set of
users and userlevels in order to hide other controls on
the form.
Thanks for the assistance on this.

Ron
-----------------------------------------------
Private Sub Form_Load() 'Current Sub

Dim sUserLevel As String

sUserLevel = DLookup
("pUserLevel", "[tblProjectMgrs]", "ProjectName=""" &
CurrentUser() & """")

If sUserLevel = 1 Then
Me.cmdOpenIBTform.Visible = False
Me.cmdOpenScriptTrackform.Visible = False
Me.IBtrackinglabel.Visible = False
Me.ScriptTrackinglabel.Visible = False

End If

End Sub
--------------------------------------
(This is the code for the 2nd Lookup but not sure how to
incorporate it into the Sub above.)

Dim rUserLevel As String

rUserLevel = DLookup
("tUserLevel", "[refTMSStrategyMgr]", "StrategyMgr=""" &
CurrentUser() & """")

If rUserLevel = 2 Then
Me.cmdOpenDMJIform.Visible = False
Me.DMJobTrackinglabel.Visible = False

End If
Click to expand...


.
Click to expand...
 
no, it's not trying to run different parts of the code at the same time. VBA
code in Access normally executes seqentially - first line, then next line,
then next line, etc. there is nothing in this code to alter that sequence.
the Null error occurs because the when the return of the DLookup() function
is Null, because you can't assign a Null value to a variable with a String
data type.

if i understand you correctly, any given user will be listed in either
tblProject Mgrs *or* refTMSStrategyMgr, but never in both tables. if that's
correct, try this:

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
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

hth
 
Ahhh that did the trick. Thank you very much!!
I completely forgot about the Nz function for working with
Null values, and the Else statement makes better sense.
And you are correct about a user never being in both
tables.

1 more question: Do you think I should I use an On Error
Statement if the person that logs in does not belong to
either table yet? Probably a good idea. Maybe something
like:
On Error Goto Err_userlevel_Load
Err_userlevel_Load:
Msgbox: "Contact Your Admin to add you to list for Entry"
Resume Exit_Form_Load

Or something to that effect.

Ron
-----Original Message-----
no, it's not trying to run different parts of the code at the same time. VBA
code in Access normally executes seqentially - first line, then next line,
then next line, etc. there is nothing in this code to alter that sequence.
the Null error occurs because the when the return of the DLookup() function
is Null, because you can't assign a Null value to a variable with a String
data type.

if i understand you correctly, any given user will be listed in either
tblProject Mgrs *or* refTMSStrategyMgr, but never in both tables. if that's
correct, try this:

Dim strUserLevel As String, strUser As String

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

With Me
If strUserLevel = "" Then
strUserLevel = Nz(DLookup
Click to expand...
("tUserLevel", "[refTMSStrategyMgr]", _
 
glad it worked. :)
i don't know how you could cause the system to generate an error. but you
can handle the "user not in either table" issue right in the If statement.
pardon me for posting the whole code again, but it's easier than posting
part of it and possibly causing confusion. see below:

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 "This user is not in either table. Whatever code you " _
& "need to handle this situation, put it here in place of -
or " _
& "along with - this message box."
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

hth
 
Of course...duh - that would be the more logical solution.
Thanks again for the help and don't worry about reposting
the whole code, it does help to curtail confusion. :)

Ron
-----Original Message-----
glad it worked. :)
i don't know how you could cause the system to generate an error. but you
can handle the "user not in either table" issue right in the If statement.
pardon me for posting the whole code again, but it's easier than posting
part of it and possibly causing confusion. see below:

Dim strUserLevel As String, strUser As String

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

With Me
If strUserLevel = "" Then
strUserLevel = Nz(DLookup
Click to expand...
("tUserLevel", "[refTMSStrategyMgr]", _
 
Back
Top