Login Access with user rights

[gbaii]

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 "ReportEven ts" but:
1. The "ReportEven ts" 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,
Gabriel

P.S. Sorry for posting this in the wrong topic.

[PianoMan64]

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 "ReportEven ts" but:
1. The "ReportEven ts" 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,
Gabriel

P.S. Sorry for posting this in the wrong topic.

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_Confi g". 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

http://support.microsoft.com/kb/207793

Hope that helps,

Joe P.

[gbaii]

Access 2007

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
endif

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,
Gabriel

[PianoMan64]

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
endif

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,
Gabriel

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.

[gbaii]

code

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.Combo employee) Or Me.Comboemploye e = "" Then
MsgBox "Trebuie introdus un nume de utilizator.", vbOKOnly, "input required"
Me.Comboemploye e.SetFocus
Exit Sub
End If

'check if there is a password

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

'check if the password match

If Me.txtpass.Valu e = DLookup("passwo rd", "users", "[IDuser]=" & Me.Comboemploye e.Value) Then
tip_user = DLookup("passwo rd", "users", "[IDuser]=" & Me.Comboemploye e.Value)
IDuser = Me.Comboemploye e.Value
Acces_type=DLoo kup("access_typ e", "users", "[IDuser]=" & Me.Comboemploye e.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"

Else
MsgBox "Incorrect password. Try again", vbOKOnly, "invalid input!"
Me.txtpass.SetF ocus
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!"
Application.Qui t
End If
End Sub

[PianoMan64]

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
endif

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,
Gabriel

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:

Sub LoginButton_Click()
    DoCmd.OpenForm "FormName", acNormal, , , , , Access_Type
End Sub

Sub Form_Open(Cancel As Integer)
    Dim strUserType As String
    strUserType = Forms!FormName.OpenArgs
    If Len(strUserType) > 0 Then
        If strUserType = "User" Then
          me.cmdbutton.visible = false
        else
          me.cmdbutton.visible = true
        end if
    End If
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.

[gbaii]

That solved my problem very nice.
Thank you.
Best regards,
gabriel