Passwording, multi-users 3

[ItIsHardToProgram]

Hello, I don't want to post any useless thread or question, or any already answered question, but I am actually needing help on multi-user passwording.... Its not really how to make a form that allows you to exploit users and etc.... its more or less to Restrain or give access, depending which user is entering the database.... and doing so with a form, not with a whole .net structure..... So basically, I was wondering if there was any way to lock (not hiding, since thats what im already doing, and its really bad)All the forms that the users don't have access to.... if so, I would need a bit of help.....

I presently have a form working with a dlookup on my user table.......

Thanks in adance.

 

[Zion7]

I use a public variable, that is assigned once a user logs in.
From my switchboard, I disable certain form buttons,
depending on who's logged in.

or


Private Sub Form_Open(Cancel As Integer)
if pstrUser = .... Then cancel = true
End Sub

 

[sophia03]

Set up .mdw security file and asign user ID for everybody. Once they log in use CurrentUser() function to determine the user ID.

if CurrentUser() = "userID" then
docmd.OpenForm "whatewer"
else
msgbox "Access denied"
exit sub
end if


Hope this helps.

 

[Melagan]

The .mdw security option is also nifty for restricting data output in SQL if you include user names or some kind of information that is relational to each user name (via user definition table) in your records. Example:

Table to track income off of sold merchandise.
You have three different sales representatives.
You have a query to calculate the total income off of sold merchandise for any given day, week, month, etc.

You want to let each of your sales people to have access to this query, but only want them to see "their" numbers and not anyone elses; you can use the CurrentUser() function as criteria for your query.

Additionally, the .mdw security lets you restrict access to each table, query, form, report, and module based on either userid, or the group that userid belongs to.


~Melagan
______
"It's never too late to become what you might have been.

 

[ItIsHardToProgram]

I don't see how the curretuser can be sent has a criteria for the query, but I find the idea very interesting... And I am quite beggining with databases so the .mdw security I am not able to set... I believe it is the one imbeded in Access, if so, when I tried setting it I locked myself out......

Thanks for your help though, I will try to work on it.

 

[Melagan]

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:

'DLOOKUP Example
Private Sub Form_Close()
IF DLOOKUP( _
  "[job_function]", "tblUsers", "[job_function] = CURRENTUSER()" _
  ) = "officer" THEN
docmd.openform "frmOfficer"
END IF

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

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

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.

 

[ItIsHardToProgram]

Sorry for not being specific I couldve saved you alot of writting, let me put you in context. Right now, I have a table with users and all informations about users, what I did since unable to set a mdw security is Load a form on startup, that hides all the tables, and that ask for user and password, its enough security for now since we don't have alot of employees.

My problem is the following, the form "userselection" works perfectly, but once the user is entered, only thing my form does is Hide or show tables,querys,forms depending on access level.

BUT I am not able to have a relation between that user name and my Timesheet querys, for example, I would like a user to only see the records related to them..... I don't know how to apply a filter that works, and I don't know how to send the return value of my startup forms to a public access variable so that it is always available on form loading.......

Thanks for your help though, maybe you answered to those questions because I did not quite get the last 2 codes, not being so familiar with coding.

 

[Melagan]

It sounds to me like you're trying to customize security procedures that Access already does. Aside from example VB and SQL above, user-level security will let you grant or deny access to any object in your database. Check out this screenshot of one of my databases:

http://home.comcast.net/~jeremiahhenry/usrlvlsecurity.jpg

In this case, members of the "Standard Group" have access to Open/Run form called "frmNewOrders", and do not have access to Modify Design, Read Design, or Administer. The same can be done for individual users if you like.

On top of the increased security this option offers, it is in my opinion that it is easier to maintain with the addition and subtraction of users.

Now as far as my code above - I understand that it is a very generic example. If you are interested, I'd be happy to help out with code more specific to your DB.


~Melagan
______
"It's never too late to become what you might have been.

 

[ItIsHardToProgram]

My problem is I have to go around that since I am unable to set groups and administrators and etc.... I cannot add users or have access recognize my username... everytime I try to do it it locks me out.

 

[Melagan]

Take a look at this site:

http://support.microsoft.com/default.aspx?scid=/support/Access/Content/SECFAQ.asp

Read "What are the steps to help protect a database." As always...back up your DB before making any huge changes, like implementing user level security



~Melagan
______
"It's never too late to become what you might have been.

 

[ItIsHardToProgram]

Thanks, I noticed I was missing some steps, the previous faq I read was not complete... Ill try it this week and start another thread if I have more problems.

Thank you very much

Julien

 

[ItIsHardToProgram]

I got it to work partialy, but i can't seem to make the filtering work..... is this only for sql?

 

[Melagan]

The SQL Recordset method runs the fastest, especially in large split databases. The DLOOKUP function works just fine for smaller applications, though.

If you share the FieldID's you've chosen for your Users table, I'll show you how the DLOOKUP function might look.



~Melagan
______
"It's never too late to become what you might have been.

 

[ItIsHardToProgram]

Ok, well I know how to use dlookup since im using it, so I have no problem finding the currentuser and associating it with an employee number, but I would need to filter my "timesheets" by employee number, so when currentuser() = john for example and when he creates a new timesheet, His employee number filters all the other entrys making them unavailable to see (in a form that is) The database is not large for now, but it could get large.
My user table:
Name/function/salaryperhour/employeenumber(p_key)/dateofhiring and some useless other junks.....
My table is linked to a main many-to-many table that is linked throughout all my info... But When I filter, nothing happens.... even if in a query...
Thanks.....

 

[Melagan]

Can you post some example data from both your user table and main table?



~Melagan
______
"It's never too late to become what you might have been.

 

[ItIsHardToProgram]

This is the timesheet table... (not all the info fit but the most important did)

no_emp Nom PreN Numéro_Feuille_Temps
6003 Bélanger Gaetan 134
6004 Yelle Claude 149
6005 Pelletier Francois 129
6008 Roy Julien-Bono 145
6003 Bélanger Gaetan 136
6004 Yelle Claude 150
6003 Bélanger Gaetan 137
6005 Pelletier Francois 142
6008 Roy Julien-Bono 146
6004 Yelle Claude 151
6008 Roy Julien-Bono 147

Here is where it gets complicated.

There is a table that is related to a subform, main form being the table i mentioned first. That subform, takes the timesheet, and spreads the hours depending on which project they were spent, so you can have 10 entrys for 1 timesheet.

Then there is my employee table that goes like this

no_emplo /Nom /Prenom /Fonction d'employé
6001 Roy Martin Ingénieur sénior
6002 Vacquerie Marc Technicien sénior
6003 Bélanger Gaetan Technicien dessinateur
6004 Yelle Claude Technicien dessinateur
6005 Pelletier Francois Ingénieur stagiaire
6006 Buckingham Gordon Technicien sénior,
6007 Bédard Martine Relation publique
6008 Roy Julien-Bono Commis

Where i have a problem is if I want to filter my timesheets, it does not filter my subform, and so, timesheets arent filtered, when accessing them. An employee should not be allowed to see another employees timesheet..... I hope you get what I mean, I would want the employee number, to filter the time sheet, and so the timesheet filters the subform.....

Don't know how it could be done, I have an idea but I have to go so ill exploit it tomorow....

Thanks for your help. feel free to add in anything

 

[PHV]

The subform should be linked to the mainform with (at least) the employee number.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

For the DLOOKUP to work, you're going to need to store information in your Users table that can relate to their UserID. The only way you'll get away with not having to do this is if you use your employee numbers as UserID's.

I'm not sure about how you want to set up your naming convention, but most choose the first letter of their first name and full last name. So, for this example:

tblUsers:

no_emplo /Nom /Prenom /Fonction d'employé /UserID
6001 Roy Martin Ingénieur sénior mroy

I like to create UDF (User Defined Function) to do the DLOOKUP so all I have to do is execute the function instead of re-type the DLOOKUP each time I want to use it.

This could go into a new module:

Public Function EmpNumber()
'DLOOKUP Function to get Employee Number from
'User table based on user currently logged in.
On Error GoTo EmpNumber_Err

EmpNumber = DLookUp( _
  "[no_emplo]", "tblUsers", "[UserID] = CurrentUser()" _
  )

 EmpNumber_Exit:
   Exit Function

 EmpNumber_Err:
   msgbox Err.Description
   Resume EmpNumber_Exit

End Function

Now your form's recordsource could be something like:

SELECT * 
FROM [tblTimeSheet] as t
WHERE t.[no_emp] = EmpNumber();

Anyone else reading this thread...how are we doing? Do you think my approch to this makes sense, or am I just spinning wheels?



~Melagan
______
"It's never too late to become what you might have been.

 

[wshs]

you can create a table with user name and password and simply create a simple log-in form.
you can filter user's id using dlookup. not much of security but it might be what you want.

 

[ItIsHardToProgram]

PHV it is linked with the employee number. I don't want to seem off route melagan, and trust me so far you have been of alot of help, but that last parcel of code goes in the loading form? before the form loads? I don't get what you mean when you say recordsource >.<. I get the main idea though....

maybe you could clarify where that code would go.

Thanks a bunch

julien