2 Queries 1 Output, Possible ? (1 Viewer)

[edisonl]

Hi,

1. Like to find out how do I compare login name with value from table 1,
results search lists in table to
in query criteria ?

2. Eg: Leave Application> Supervisor Login> Display Lists of Names FROM
Personnel_Table (ONLY) Where Dept= '" & LoginID!Dept & "'" & " AND
AccessRights > '" & LoginIDAccessRights'" FROM PendingLeave_Table [PS:

Higher Authority = Lower AccessRights Number]

3A. Personnel_Table:
__________________________________________________________________
UserID Name Dept AccessRights
016 Ann Accounts 3
018 Mike Accounts 4
014 Zen Accounts 4

017 June Accounts 2
024 John Production 4

3B. PendingLeave_Table:
__________________________________________________________________
UserID From To
AppliedDate
019 22/11/08 22/11/08
13/11/08
018 28/12/09 31/12/08
30/10/08
014 28/12/09 31/12/08
30/10/08
024 13/11/08 14/11/08
09/09/08
017 13/11/08 14/11/08
09/09/08

Therefore , When Ann (UserID:16) Login She Can only view Zen(UserID:14),
Mike (UserID:18)
leave request. Others due to different access rights & department constraint
she wouldnt be able to view.

 

[DrSnuggles]

1. When user logs on save the user's Dept ID as a Public Variable
eg.
' g = global, int = integer
gintDeptID = DLookup("[dept_id_fieldname],"tablename","[username_fieldname] = '" & logon_name & "')


2. Create a public function in a module eg:
Public Function getDeptID() As integer

getDeptID = gintDeptID

end function

3. In your query under the column for dept_id_fieldname you can put this in the criteria getDeptID().

Et Voila!