This FAQ leads to a great link that describes working with and implementing .mdw security in great detail: faq181-83
As for the currentuser() function, this is how I use it to "filter" SQL.
Each user, ofcourse, has a unique UserID. I put all of those UserIDs in a table, along with their first name, last name, email address, job_function, job_title, etc.
Say you have a data entry form for "clerks", and a seperate data entry form for "officers". In your DB, you can use a startup form to automatically point the user to their appropriate data entry form using SQL or a DLookUp. EX:
Code:
'DLOOKUP Example
Private Sub Form_Close()
IF DLOOKUP( _
"[job_function]", "tblUsers", "[job_function] = CURRENTUSER()" _
) = "officer" THEN
docmd.openform "frmOfficer"
END IF
Code:
'SQL Example
Private Sub Form_Close()
dim rst as Recordset, SQLText
dim user_function as String
SQLText = "SELECT * FROM tblUsers AS u WHERE " _
& "u.[UserID] = CurrentUser()"
SET rst = CurrentDB.OpenRecordset(SQLText)
user_function = rst![job_function]
'Now you have the user's job_function stored in
'the variable user_function, and can use that variable
'to determine which form to open.
As for queries and reports, it depends on how you're tracking names in your main tables. Again, I will use a point-of-sale type of example. In this case, this DB is for a shoe department and there are currently three salespeople: John Doe, Jane Doe, and Ace Cobbler. Let's say you create UserID's for all of them in the .mdw security file as johndoe, janedoe, and acecobbler, respectivly. Now in a commisson-based sale, each record is probably going to have some field to identify which sales rep gets credit for the sale, and perhaps how much the sale was for. (There is likely a query and/or report to figure the actual commission percentage).
Anyway, now for the cool part... since each sales rep has to log into the database with their own name and password, the CurrentUser() function can be used to filter out the queries that are used to calculate the numbers for the reps. Assuming on data entry, the sales reps are input as their proper name and not their UserID (hopefully from another lookup table to avoid data-entry errors), which is usually the case.
We need a function that will relate CurrentUser() to their true name. I do this using the same idea as above.
Code:
Public Function TrueName()
dim rst as Recordset, SQLText
SQLText = "SELECT * FROM tblUsers WHERE " _
& "[UserID] = CurrentUser()"
Set rst = CurrentDB.OpenRecordset(SQLText)
TrueName = rst![Full_Name]
End Function
Bringing the idea home...
Code:
SELECT t.[primary_key], t.[Sales_Rep], t.[Sale_Date], t.[Sale Amount], Format(t.[Sale_Date], "mmm yyyy") as Report_Month
FROM tblShoeSales as t
WHERE Report_Month = "APR 2006" and t.[Sales_Rep] = TrueName();
Please forgive the lengthy post; it sounds way more complicated than it really is. I've found a lot of value in using CurrentUser() in this way, though, and thought I'd share some ideas with any who were interested.
~Melagan
______
"It's never too late to become what you might have been.
