Default Select-Value Query value (Access 2002)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

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?

Reply | Quote

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%5B/url%5D

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

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

[indent]

---

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

---

[/indent]

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…)

Reply | Quote

Please continue the discussion in the thread starting at post 476131. To avoid duplication, this thread is locked.

Reply | Quote