By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Login Access with user rights

P: 5
Hi all. Being new to access and wanting to build some kind of basic CRM software, I have the following problem. I have a table called users with the fields: UserID, UserName, UserPass and UserType. There are let's say 2 records with "admin" word in the UserType field and 3 more records with the "user" word in the same field. After the user is putting his password and hit login button, i need to open the "base" form.
This "base" form has 3 buttons: "AddEvent" "ViewEvents" and "ReportEvents" but:
1. The "ReportEvents" must not be visible by the users that does not have "admin" in the UserType field of his UserName
2. After login, if the user hit the "ViewEvents" button, they must view only the records that he put in.

How can i do that, please?

Thank you.
Best regards,

P.S. Sorry for posting this in the wrong topic.
Mar 2 '09 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 374
Hey gbaii;

Welcome to Bytes, first of all.

Well, without any more specifics in regards to your project, the way that I've solved this issue in the past is as follows.

I create a MDW file, that is available from any version of MS Access 97 and up to 2003. 2007 Accdb does not support it.

This MDW file will store the username and password of each user and there respective permissions to Tables, queries, forms, reports, and macro accesablility rights. You would then also create a table called "User_Config". In this table you would store the username and make that the primary key to the file, as well as any additional settings that are dealing with button rights aka available features of the program. the easiest way that I've found to do that unless you have several levels of support, is to simply create a Yes/No field, and if it is checked, then the user is an Admin, if it is not checked, then they aren't.

between the two files that are managed by Access, this would give you the detail control over how to pull users and what rights, and access they have.

Here is a link that may be able to help you with some of the issues with setting up security on MS Access

Hope that helps,

Joe P.
Mar 3 '09 #2

P: 5
Hi. Thx for the answer you provided. But it doest help me because I use Access 2007 as I stated in the subject. I shall give you more details: I use a table with the fields: IDuser, username, password, access_type. and in this table I have 4 records. Two of them has in the access_type field the word "admin" and the other two has the word "user". In my login form i have a combo box where are the names of the people that can access the database, a text box for the password they type in and the "login" button. When someone click the "login" button, the propterty "on click" of the button goes to VB code that says something like:
if the user put the right pass then
store in some variable with the dlookup function his access_type
open the main form

now my question is:
how do i open my main form in admin mode, that meaning that this form must show some buttons that are not visible if the user has "user" in the access_type field.
Hope that I was clear enough. If not, tell me if you need me to show u the exact code.

Thank you again.
Best regards,
Mar 3 '09 #3

Expert 100+
P: 374
Hey gbaii,

No where in your post did you state that you were using MS Access 2007 until your recent post.

Keeping that in mind. if you want to build a a form that gives you the same functionality as previous versions, then I have one question first?

1. when creating your file, did you select ACCDB or MDB when you created your file?

if you selected MDB, then the security feature is still available to you, if you selected Accdb, then they aren't.

I'm assuming that you select the the accdb format.

You have a great deal of coding that you're going to have to do in order to:

1. Create a function that will return the current user.
2. create a function that will allow you to test what rights the user has?
3. create a function(s) to Be able to manage Username and passwords for all user.

If you can break down all those steps and ask each question, one at a time, that would be alot easier than me just simply giving you the answer to the question. How do I do it all.

Hope that helps,

Joe P.
Mar 3 '09 #4

P: 5
Hi again. You were right. it's a accdb.
the code for the login button looks like this:

Private Sub Command5_Click()
'check if there is a user name

If IsNull(Me.Comboemployee) Or Me.Comboemployee = "" Then
MsgBox "Trebuie introdus un nume de utilizator.", vbOKOnly, "input required"
Exit Sub
End If

'check if there is a password

If IsNull(Me.txtpass) Or Me.txtpass = "" Then
MsgBox "Enter password.", vbOKOnly, "Input required"
Exit Sub
End If

'check if the password match

If Me.txtpass.Value = DLookup("password", "users", "[IDuser]=" & Me.Comboemployee.Value) Then
tip_user = DLookup("password", "users", "[IDuser]=" & Me.Comboemployee.Value)
IDuser = Me.Comboemployee.Value
Acces_type=DLookup("access_type", "users", "[IDuser]=" & Me.Comboemployee.Value)

'close form and open main form

DoCmd.Close acForm, "login", acSaveNo
"""" here must be some code to open the "mainform" based on "Acces_type"

DoCmd.OpenForm "mainform"

MsgBox "Incorrect password. Try again", vbOKOnly, "invalid input!"
End If

'if user input the wrong password 3 times, close program

nrincerclog = nrincerclog + 1
If nrincerclog > 3 Then
MsgBox "no database access.", vbCritical, "Acces restrictionat!"
End If
End Sub
Mar 3 '09 #5

Expert 100+
P: 374
Hey gbaii

Sorry I didn't see your question.

When the login screen comes up, when the user has successfully logged in, then you would simply do a:

Expand|Select|Wrap|Line Numbers
  1. Sub LoginButton_Click()
  2.     DoCmd.OpenForm "FormName", acNormal, , , , , Access_Type
  3. End Sub
  5. Sub Form_Open(Cancel As Integer)
  6.     Dim strUserType As String
  7.     strUserType = Forms!FormName.OpenArgs
  8.     If Len(strUserType) > 0 Then
  9.         If strUserType = "User" Then
  10.           me.cmdbutton.visible = false
  11.         else
  12.           me.cmdbutton.visible = true
  13.         end if
  14.     End If
  15. End Sub
that way, when the form opens, you know the type of user. I would suggest, that if you pass it the username, then that way, you would have the user information that you could lookup as well.

Hope that helps,

Joe P.
Mar 3 '09 #6

P: 5
That solved my problem very nice.
Thank you.
Best regards,
Mar 5 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.