• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

How to filter records using user logon

I have a table of items that belong to the following organizations.
OrgCode = "111"
OrgCode = "123"
OrgCode = "124"

I want someone to only view the records for their org.   The employee is given an lexiographic access code:
MyOrgID = "1" - to see all records in organizations that begin with "1"
MyOrgID = "12" - to see only records in organizations that begin with "12"
MyOrgID = "123" - to see only records in organizations  "123"

I set up some public variables and populate them from the employee table when they logon:
gMyOrgID = "12"
gLenMyOrgID = len(trim(gMyOrgID)) = 2  

Why can't I set the selection criteria in the query to match the global variable?
   myFilter: Left([OrgCode,2)  = gMyOrgID
0
PTaylorM2
Asked:
PTaylorM2
  • 4
  • 3
2 Solutions
 
dqmqCommented:
Where are yo trying to apply that filter?
0
 
PTaylorM2Author Commented:
When I allow the user to browse the database.   They can select from the dataset to view the individual records.    I'll also use it when I generate reports.

Note:  the last line should have read
   myFilter: Left([OrgCode,gLenMyOrgID )  = gMyOrgID
0
 
PTaylorM2Author Commented:
The key question I have is why does the query not permit me to use a global variable as the criteria the dataset?    ...
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
dqmqCommented:
I'm tryiing to understand exactly what you mean by "query" and "filter".  You can reference the global variable in a VBA code that influences a querydef, for example, but you cannot reference a global variable in a query.  

You can create a VBA function that returns the global variable and uses that in a query.

I may be speaking to soon, but I think you may be better served by saving your access codes in a table.  But then I see they already originate from the employee table, so I'm a little confused why you are going the route of global variables.
   
0
 
PTaylorM2Author Commented:
My thought was, when the employee logs on, to lookup their org access in the employee table and store that in a global variable to use in the criteria of the queries.
Are you suggesting that I do a data lookup function for that value when I need it?....I'd still need to store the employee ID in a global variable to do the DLookup, right?

One possible solution is to store the employee data in a text box on the log-in screen and leave it open so I can reference those values.  is that too clumsy?
0
 
kdart301Commented:
Hi,

When I have to seperate user data per login, I create a hidden form and text boxes and append the applicable information after login and run the queries off of that field. In this case in your query you could use something like:

Like [Forms]![frmHidden]![MyOrgID] & "*"

anyone that begins with a 1 would see everything, 12 would see 12 and 123 and so on.

Hope this helps.

Ken
0
 
dqmqCommented:
>I'd still need to store the employee ID in a global variable to do the DLookup, right?

That's an example of what I would call a "session variable: a value you want to persist for the duration of a session.  Since Access doesn't support session variables, I store them in hidden fields on my main form which hangs around for the duration.  During testing, I can "unhide" the fields and manipulate them to test different scenarios--in your case, test your queries against different access codes.

0
 
PTaylorM2Author Commented:
The solutions are essentially the same, one that I suggested when posing the question.  It just seems odd that a gloabl variable can't be used in  a query.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now