queries cannot see variables of any kind. You can embed a function in a
query. Also, if there are no arguments passed to a query in a function, it
will execute only one time rather than for each record.
The trick is to create a function that calls the username function and pass
the flag as an argument. You function should look at the flag and determine
whether the user has rights to view the record. What I don't know is how you
know which users have rights to see the records. For example purposes, I
will assume you have a table of users with a field that shows what rights
each has. The code below is totally untested air code, so it may take some
tweeking to work correctly.
Add a Calculated field to your query that will call the function which will
return True or False and set the criteria for the field to True. In the
function call, pass the flag to the function.
Here would be your field definition:
ShowRec: CheckFlag([Flag])
Function CheckFlag(strFlag As String) As Boolean
Static strPermission As String
Static strUser as String
If strFlag = "N" Then
CheckFlag = True
Exit Function
If Len(strUser) = 0 Then
strUser = GetUserName
strPermission = Nz(DLookup("[Permission]", "UserTable", "[UserID] =
'" & strUser & "'"), "")
End If
If Len(strPermission) = 0 Then 'Unknow User, Dont Show
CheckFlag = False
ElseIf strPermission = "Mgr" Then '(This is whatever says they have
permission)
CheckFlag = True
Else
CheclFlag = False
End If
End Function
Note the varialbes in this function are Static. That means they retain
their value between calls to the function. The reason in this case if for
performance. If we already know what we found in the user table, it is not
necessary to pay the overhead of a DLookup for each record.
Me said:
Hi Klatuu,
I am trying to specify the UserName in Query and then use the query
in form, its not wokring. It asks for UserName. On launch of my access
project,
I run this form which calls the function to get the username, I move username
to Public variable in the query. But still when I run the query it asks me for
UserName.
My question is how do I call the function in query to get the UserName and
then
do the rest ...
Thank you,
-Me
:
Here is a function that will return the userid.
Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
Public Function GetUserID()
' Gets the userid (5+2) of the current user.
' Put the following in the declarations section:
'Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String,
ByRef intN As Long) As Long
' modified 4/11/02 dbk set default userid for those not on network
'set default to xxx, changed 10 to 20 for length on 2 lines
Dim Buffer As String * 20
Dim Length As Long
Dim lngresult As Long, userid As String
Length = 20
lngresult = GetUserNameA(Buffer, Length)
If lngresult <> 0 Then
userid = Left(Buffer, Length - 1)
Else
userid = "xxxxxxx"
End If
'MsgBox "USERID = " & userid, , "GetUserID()"
GetUserID = UCase(userid)
End Function
:
Klatuu,
Yes, I can certainly include the userid in query, what's the syntax to use
the userid?
'cause I am not using Access security, I am using the sql server security.
Or on Form_Current, I can find out who is logged on, at that time is there
any way to modify the criteria?
Thank you for your help!
-Me
:
Use a query based on the table as the record source of the form. Include a
filter criteria in the query that includes or excludes the flagged records
based on the user id.
:
I have a form to display records based on a flag in database table.
All users should be able to view records flagged 'N' and
only those users with some particular userid should be able to view
records flagged 'Y'.
What's the best way to achieve this?
Thank you,
-Me
