Solved Change Row Source combobox depend of function "GetAccessLevel" (1 Viewer)

tihmir

Registered User.
Local time
Today, 09:41
Joined
May 1, 2018
Messages
257
Hi all. I need some help please.
I am trying to change Row Source of ombobox depend of function "GetAccessLevel"
I have

Code:
    Public Function GetAccessLevel()
        GetAccessLevel = Nz(DLookup("AccessLevel", "tblUsers", "UserName='" & GetUserName & "'"), 0)
    End Function

and I have combobox with name "cboInspectors" and Row Source: tblInspectors and Row Sours Type: Table/Query

What I want is:
If I have GetAccessLevel = 1 then
I have the whole list in tblInspectors to be able to be chosen and visible into cboInspectors

Else If GetAccessLevel = 2 then
the list into cboInspectors to be only with name with the name that was logged

I try with this but it doesn't work:
Code:
Private Sub Form_Load()
    If GetAccessLevel = 1 Then
        Me.cboInspectors.RowSource = "qryInspectors"
        Me.cboInspectors.RowSourceType = "Table/Query"
        
    ElseIf GetAccessLevel = 2 Then
         Me.cboInspectors.RowSource = "qryWelcomeUserDZK"
         Me.cboInspectors.RowSourceType = "Table/Query"
    End If
End Sub
 

Ranman256

Well-known member
Local time
Today, 12:41
Joined
Apr 9, 2015
Messages
4,339
rather than hard code things, ALWAYS put values in a table as much as you can:
table: tLevelQrys
Level, Qry
1, qryInspectors
2, qryWelcomeUserDZK

once you get level from Dlookup, get the qry:
getLevelQry = Nz(DLookup("Qry", "tLevelQrys", "[Level]=" & GetAccessLevel() ))


Me.cboInspectors.RowSource =getLevelQry()

NOTE: make sure your LEVEL fields are strings or numerics:
your GetAccessLevel function says these 'numbers' are strings. If they are numbers , remove the single quotes.
 

isladogs

MVP / VIP
Local time
Today, 16:41
Joined
Jan 14, 2017
Messages
18,186
No need to create/use saved queries for this (though you can do so if you prefer)
I would suggest using something like this in the Form_Load event

Rich (BB code):
Private Sub Form_Load()

Select Case GetAccessLevel

Case 1 'view all records in combo
   Me.cboInspectors.RowSource = "SELECT * FROM tblInspectors" 'or limit to specified fields

Case 2 ' view logged on user only
   Me.cboInspectors.RowSource = "SELECT * FROM tblInspectors WHERE UserName='" & GetUserName & "'"

End Select

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:41
Joined
Sep 21, 2011
Messages
14,044
I would have thought GetUserName would be GetUserName()
 

tihmir

Registered User.
Local time
Today, 09:41
Joined
May 1, 2018
Messages
257
Thank you very much for the help and advice you gave me, Ranman256, isladog!
With the code of Ranman256 it works. But by the method of isladogs the code does not work.
I think the function does not want to start. I don't know why? My combobox remains blank.
I will try to find out what is the reason for it not working
 

isladogs

MVP / VIP
Local time
Today, 16:41
Joined
Jan 14, 2017
Messages
18,186
Excellent. So, for the benefit of others reading this thread, was there an error in your code or was it in mine?
 

tihmir

Registered User.
Local time
Today, 09:41
Joined
May 1, 2018
Messages
257
Excellent. So, for the benefit of others reading this thread, was there an error in your code or was it in mine?
@isladogs, Your code works perfectly!
It's my a silly mistake! I just have forgotten to add users with AccessLevel 2!
 

Users who are viewing this thread

Top Bottom