How to filter records using user logon

Posted on 2011-05-09
Last Modified: 2012-08-13
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
Question by:PTaylorM2
    LVL 42

    Expert Comment

    Where are yo trying to apply that filter?

    Author Comment

    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

    Author Comment

    The key question I have is why does the query not permit me to use a global variable as the criteria the dataset?    ...
    LVL 42

    Expert Comment

    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.

    Author Comment

    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?
    LVL 2

    Accepted Solution


    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.

    LVL 42

    Assisted Solution

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


    Author Closing Comment

    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.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Access VBA Set Struing To %SYSTEMROOT% 3 19
    How get a scanned image on a report 4 16
    using Access 8 38
    SQL syntax error in VBA 11 20
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now