Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Select-Value Query value (Access 2002)

    I have numerous queries I am creating in my much-less-pieced-together database, and before I start making them, here is my goal:

    I have a table of user names and passwords. With each user name is a company code assigned to them, a, b, c, h, or w.

    When the user logs in, I want their company code to be assigned as the default query value, filtering so that the user can only see their own company while perusing about.

    Any help/suggestions?
    Thanks a lot!

    Jeremy
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default Select-Value Query value (Access 2002)

    Where is the company code to be found? In the same table that contains the user names and passwords, or elsewhere? What are the relevant table and field names?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Select-Value Query value (Access 2002)

    The company code is in the table with user names, called tblUsers. The Columns are [ID] (AutoNumber), [UserName], [Password], and [Company]. As for a query example, I would have a query called qryPersonnel, with the fields [Rank], [Name], [SSN], and [Company]. I want the user to only see values from their own Company while running that query.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default Select-Value Query value (Access 2002)

    Thanks. Set the criteria for Company in qryPersonnel to

    DLookup("Company", "tblUsers", "UserName=" & Chr(34) & CurrentUser() & Chr(34))

    (this assumes that you have applied user-level security with the usernames and passwords from the table)

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Select-Value Query value (Access 2002)

    Ok, now here's a tricky part. I have a group of power-users (myself and the administrators in my shop) who will use the same login, but in tblUsers, they do not have a company code. Must I enter the query criteria as :

    "DLookup("Company", "tblUsers", "UserName=" & Chr(34) & CurrentUser() & Chr(34)) Or DLookup("Company", "tblUsers", "UserName=" & Chr(34) & CurrentUser() & Chr(34)) Is Null" ??

    I haven't come to that point yet, but I'd like to figure out the answer before I get there.

    Also - I think I may have "jumped the gun" in form creation..I created it from my table, instead of a query. Is it possible to change just that one field (Company) on the form to apply the filter to the forms?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default Select-Value Query value (Access 2002)

    That should work (just to make sure: without the quotes surrounding the entire expression).
    When you specify the criteria like that, than save, close and reopen the query, it will look different. This is normal and nothing to be worried about.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default Select-Value Query value (Access 2002)

    I don't understand the question you added later. Could you please elaborate?
    BTW, you can change the Record Source of the form from a table to a query, if necessary.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Select-Value Query value (Access 2002)

    Awesome. I'm kinda replying to redirect your attention back here, I edited my post...you'll see the second predicament I've made for myself. I tried setting the control of the Company field to [qryFilterByCo]![Comp] (my query name and company column that containes the filter criteria), but it returns #Name?

    Alas, you are always one step ahead. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    I didn't have the DLookup criteria in the query yet. Now that I've entered it, I don't get the prompt. Now though, how do I get the database to recognize the login from my login form as CurrentUser?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Select-Value Query value (Access 2002)

    Ok. I'm having trouble getting Access to compare my combo box and password with the table tblUsers. I'm using the info from this site:
    http://www.microsoft-accesssolutions.co.uk/login.htm

    It always ends up highlighting the DLookup line, claiming I canceled the previous operation. What gives? Hmmph. And I've yet to figure out how to store the UserName for future reference (like in the query criteria posted earlier)....
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default Select-Value Query value (Access 2002)

    Are you sure your link is correct? I get "Server not found".

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Select-Value Query value (Access 2002)

    Actually, I think their server might be down. I've tried that site for the past few hours...it's definitely the site. You could google it if you'd like. One of those days for them I guess...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default Select-Value Query value (Access 2002)

    I was able to view Google's cached version of the page. If you have a problem with one of the lines using DLookup, there is probably an incorrect field or table name in it.

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Select-Value Query value (Access 2002)

    Hm. well, when I get the page to work I'll be able to get the code again (I errantly deleted it when I was messing with the form), and I'll study it further...
    Thanks.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  14. #14
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Default Select-Value Query value (Access 2002)

    <hr>(this assumes that you have applied user-level security with the usernames and passwords from the table)<hr>

    So I completely missed that. Now that we know I haven't any user level security applied....where do we go from there? (I realize the obvious way would be to apply user level security...that's something I've yet to meddle in, I've secured my database well enough to keep honest people honest so far, and it's worked...)
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Default Select-Value Query value (Access 2002)

    Please continue the discussion in the thread starting at <post#=476131>post 476131</post#>. To avoid duplication, this thread is locked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •