User Login Form Help

[andybuck86]

Hi all,


I'm new to Access and new to the forum so please go easy on me


I was hoping someone could help me with a form on a database I'm designing. The database is a simple employee database but I need to restrict access to some employees with certain users.

I.e. Hire Manager can only access hire employees, accounts manager can only access accounts employees etc.


I have all the employees in one table called tblEmployees. Each record has a field called 'location' which shows which department they are in.

I have a table called tblUsers with User Names and passwords. I have also created a simple user login form with the following code beind a login button.

Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box
If IsNull(Me.CboUser) Or Me.CboUser = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.CboUser.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.TxtPassword) Or Me.TxtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.TxtPassword.SetFocus
Exit Sub
End If
'Check value of password in tblUsers to see if this matches value chosen in combo box
If Me.TxtPassword.Value = DLookup("UserPassword", "tblUsers", "[UserID]=" & Me.CboUser.Value) Then
MyUserID = Me.CboUser.Value
'Close logon form and open splash screen
DoCmd.Close acForm, "frmLogin", acSaveNo
DoCmd.OpenForm "View/Amend Employee Records"
Else
MsgBox "Password Invalid. Please Try Again", vbCritical + vbOKOnly, "Invalid Entry!"
Me.TxtPassword.SetFocus
End If
'If User Enters incorrect password 3 times database will shutdown
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub

The code works fine as a login prompt but I need help with restricting access to certain records within the employees table.

I also need to block access to any tables/queries etc for all users until logged in. Currently users can avoid the login form and access/edit information.

Is this possible?

Thanks in advance for any help!!


Andy

 

[Dewi]

One solution would be to ensure all your forms are hidden to start with, i.e on startup , hide the navigation menu and auto exec the login form. Then on successful login make a series of command buttons visible on the form,or alternatively open a menu screen. You can then make reference to the users "Department" from the login screen, and filter the forms to open based on this value using the DoCmd OpenForm option....

Make an array called sWhere as a string then use it to populate the docmd openform command...

'Comment out or delete the line that does not apply --
'If the ID is a number, build the WHERE clause like this:
'sWHERE = "[Department] = " & Me.[Department] *****this code works when the field used is a number

'If the Department is a text value, you need to surround the Department in single quotes
sWHERE = "[Department] = '" & Me.department & "'"
'Open the form
DoCmd.OpenForm "form1", acNormal, , sWHERE

hope this helps

 

[Isskint]

Dewi's method is good.

You could also allocate access levels to your users and include the required access level in each Forms (or indeed a reports) tag property. Then in a Forms OnOpen event DLOOKUP the users access level and compare it with the required level for the form.

As for 'storing' the current user, either pass it form to form as the OpenArgs value or (easier i think and the way i do it) add a textbox to your menu forms which you pass the user name to after logging in. This way the user name is always available.

Have a look at the Bob Larsons Simple Login Sample at http://www.btabdevelopment.com/ts/samples