Opening forms for different users! (1 Viewer)

[Charlottew14]

Hi,

I don't know if anyone can help, but I have an access database with three main user groups.

They're all similar, but I want to be able to hide certain information from certain groups.

I'm looking for a piece of code, or macro, so that when a user clicks on something, depending who they are a different form opens.

I'm still very new with access, so I'm finding all the coding very overwhelming!!

Many thanks in advance

 

[speedball]

What version of Access are you using?

Access 2003 has additional security settings that you can make use off for allowing access to different forms.

Access 2010 doesn't have this but you should still be able to achieve the same end result using a different approach. I think that 2007 is the same as 2010 in this respect.

 

[Charlottew14]

I'm using 2010 - I can't figure out how to do it, other than duplicating the forms to give me two trails, which obviously isn't very productive!

 

[speedball]

Here's how I do it in my database.

1. Have a frmUserLogin form that opens when the database is first opened. Achieve this with File/Options/Current Database/Display Form:

2. Make sure that the form properties of frmUserLogin as set to 'Modal' to prevent the user from bypassing it when they enter the database.

3. frmUserLogin has a txtLoginName textbox and txtPassword textbox, both of which are unbound, i.e. not linked to any tables.

4. The user types in their user name and password and clicks on a btnLogIn button

5. The OnClick event of btnLogIn runs the following VBA:

Private Sub btnLogIn_Click()
    DoCmd.SetWarnings False
 
    Dim UserName As String
    Dim Password As String
    UserName = Me.txtUserLogIn.Value
    Password = Me.txtPassword.Value
 
    Dim myCon As ADODB.Connection
    Dim myRS As New ADODB.Recordset
 
    Set myCon = CurrentProject.Connection
    myRS.ActiveConnection = myConnection
    myRS.Open "SELECT UserName, UserAccess FROM tblUsers WHERE UserName = " & UserName & " AND Password = " & UserName & ""
 
    If myRS.RecordCount > 0 Then
        DoCmd.Close acForm, "frmUserLogin"
        DoCmd.OpenForm ("frmSwitchboard")
        Forms!frmSwitchboard.txtCurrentUserName.Value = rs.Fields("UserName")
        Forms!frmSwitchboard.txtCurrentUserAccess.Value = rs.Fields("UserAccess")
    Else
        GoTo VerificationFailed
    End If
 
    myRS.Close
    myConn.Close
    Set myRS = Nothing
    Set myConn = Nothing
 
    DoCmd.SetWarnings True
 
    Exit Sub
 
VerificationFailed:
    MsgBox "Login failed." & vbCrLf & vbCrLf & "Please check your Login and Password and try again.", , "Login failed"
 
    myRS.Close
    myConn.Close
    Set myRS = Nothing
    Set myConn = Nothing
    DoCmd.SetWarnings True
 
End Sub

6. You will also need a frmSwitchboard form with a txtCurrentUserName textbox and a txtCurrentUserAccess textbox. These can be set to non visible if you don't want the users to see them.

7. frmSwitchboard needs to remain open all the time so that you can reference txtCurrentUserName or txtCurrentUserAccess.

8. When opening forms that are conditional on the user, you can include the code like this within the On_Click event of the button.

If Forms!frmSwitchboard.txtCurrentUserAccess.Value = "[condition]" Then
    'Code to open form A
Else
    'Code to open form B
End If

9. Almost forgot, you will also need a tblUsers table with the following fields: UserName, Password, UserAccess. This will need to be prepopulated with the necessary details.

Hope this helps.

Please be aware that I'm pretty new to Access 2010 and fairly new to Access overall. I'm usually on here asking for help from other people and this is my first helping someone else post! Hopefully one of the more experienced users will comment on whether this is an okay approach to take or suggest any better alternatives.

Let me know how you get on
Speedball

 

[Charlottew14]

Thank you so much for your reply, I think I understand it!

I do have a couple of questions though - on the Login code, where does it say that if the UserAccess is this, then open this form, if it is that, open that form?

And also, what exactly needs to be on the switchboard form....I'm not sure I understand the difference between that one and the Login form....

I've never had anything to do with VBA or coding before so it's all seeming very complicated, but I'm determined to get my head around it

Thanks again!

 

[pr2-eugin]

Hello Charlotte, what speedball has provided is the way to go around to achieve this, for this I am using speedball's word and my suggestion as well..

9. Almost forgot, you will also need a tblUsers table with the following fields: UserName, Password, UserAccess. This will need to be prepopulated with the necessary details.

You need to design this first, the UserAccess will categorise them, in my DB I have four levels 'Customer Service', 'Tele Sales', 'Manager', 'Admin'. So based on the UserAccess you will decide :
(a) Which form to open or
(b) Which buttons are available for which user.

1. Have a frmUserLogin form that opens when the database is first opened. Achieve this with File/Options/Current Database/Display Form:

2. Make sure that the form properties of frmUserLogin as set to 'Modal' to prevent the user from bypassing it when they enter the database.

3. frmUserLogin has a txtLoginName textbox and txtPassword textbox, both of which are unbound, i.e. not linked to any tables.

4. The user types in their user name and password and clicks on a btnLogIn button

5. The OnClick event of btnLogIn runs the following VBA:

Well the code is a bit complicated as it can be replaced with simple DLookUp. I am editing it, but you might as well use speedball's code.

Private Sub btnLogIn_Click()
 
    Dim UserName As String
    Dim Password As String
    Dim UserAccess As String
    UserName = Me.[COLOR=Blue]txtUserLogIn[/COLOR].Value
    Password = Me.[COLOR=Blue]txtPassword[/COLOR].Value
  
    UserAccess = Nz(DLookUp("UserAccess","tblUsers","UserName = " & UserName & " AND Password = " & UserName & ""),"N/A")
 
    If UserAccess <> "N/A" Then
        [COLOR=Blue]DoCmd.Close acForm, "frmUserLogin"
        DoCmd.OpenForm ("frmSwitchboard")
        Forms!frmSwitchboard.txtCurrentUserName.Value = rs.Fields("UserName")
        Forms!frmSwitchboard.txtCurrentUserAccess.Value = rs.Fields("UserAccess")[/COLOR]
    Else
        GoTo VerificationFailed
    End If
 
    Exit Sub
 
VerificationFailed:
    MsgBox "Login failed." & vbCrLf & vbCrLf & "Please check your Login and Password and try again.", , "Login failed"
 
End Sub

The following code will be used on the switchboard form button's, OnClick first check the UserAccess, and give them access to Form or deny it..

8. When opening forms that are conditional on the user, you can include the code like this within the On_Click event of the button.

If Forms!frmSwitchboard.txtCurrentUserAccess.Value = "[condition]" Then
    'Code to open form A
Else
    'Code to open form B
End If

I normally only display the buttons based on the UserAccess. In the Form Current property of the Switch board I have a Switch Case that hides or displays the buttons. Which I find It more easy than to design specific forms for specific AccessLevels. Hope this helps..

 

[The_Doc_Man]

I use a similar table that associates each user to a role. The user's role is then tested in the FORMOPEN event for each form opened by the dispatch/switchboard form. You can pass that in via the .OPENARGS parameter of the DoCmd.OpenForm method, and the form itself can see what was passed by testing ME.OPENARGS (as a property). After that, you can let each form decide for itself whether it wants to stay open or offer the user a serious raspberry icon. That would also tell you whether your user tried to bypass the switchboard, since you don't get an OPENARGS value from a direct form open using the navigation pane.

In fact, a major key to all of this is that you must assure that you disable the user from opening the navigation panel directly. Otherwise you have no control over security anyway. You can search this forum for all of the issues related with that action. Basically, though, with Ac2010 you do not have inherent user-level security. You have only the tools you need to "roll your own." Tedious, but eminently possible.

If you are in a formal Windows domain environment where a user logs in to a workstation via Windows security methods, you might be able to bypass building your own username dialog if you trust the domain's login security.

If so, you can do an Environment lookup on "USERNAME" to see who the domain thinks this person is. If you are lucky enough to be in that kind of situation, you don't even need a password. Basically, your two choices are to trust the domain or to define a table with username and password. In the latter case, it depends on how well you trust your users as to whether you need to encrypt the password.

 

[speedball]

Hello Charlottew14

You may have now figured these out already, but just to answer your specific questions:

"on the Login code, where does it say that if the UserAccess is this, then open this form, if it is that, open that form?"

The frmUserLogin form is used purely to identify who the user is and to verify that they are who they say they are by requesting a password. The information that they supply is then compared against inforamation that is already stored in tblUsers

"And also, what exactly needs to be on the switchboard form"

This can be considered as the first main page of the database and will contain buttons to navigate to the other forms that you have created. But now, when a user clicks on a button on the switchboard form (or any other form if you wish) you will be able to perform a check to see if their user level will permit them to open that form. Or you can decide which particular form they navigate to based on their user level.

Just to explain the code a bit more, I've created a recordset and populated it with the UserName and UserAccess, but only if the UserName and Password match to a record in the tblUsers table (myRS.Open "SELECT UserName, UserAccess FROM tblUsers WHERE UserName = " & UserName & " AND Password = " & UserName & "")

I've then checked to see if the recordset contains any records, which it will only do if the UserName and Password supplied by the user matched with a record in tblUser (If myRS.RecordCount > 0 Then)

pr2-eugin has used DLookUp to perform a similar check, which I agree makes it less complicated. However, there is a small typo in pr2-eugin's code - in the DLookUp line, it should read:

UserAccess = Nz(DLookUp("UserAccess","tblUsers","UserName = " & UserName & " AND Password = " & Password & ""),"N/A"

(Let's ignore the fact that the typo originated from my code and move on )

The DLookUp function has been wrapped in an Nz function to deal with Nulls.

The Doc Man makes a good point about disabling the navigation panel. I've recently become aware of MS Access 2010 Runtime. This is free to download and will allow you to distribute the database to users who don't have Access 2010. Most importantly it has none of the features of Access and so users can't bypass any of the controls you've put in place, or see the underlying tables. Only downside is that any unresolved errors in your code will cause the database to crash rather than just giving an error message. You can see what the runtime version will look like by changing the file extention from .accdb to .accdr (though this is just for testing and will not stop the user from changing it back to .accdb!)

Hope that helps
Speedball

 

[pr2-eugin]

Speedball, I do not think there is a typo in the DLookUp, however there is a syntax error.. which I did see when you mentioned there is a typo, I would be glad if you could point out the typo..

pr2-eugin has used DLookUp to perform a similar check, which I agree makes it less complicated. However, there is a small typo in pr2-eugin's code - in the DLookUp line, it should read:

UserAccess = Nz(DLookUp("UserAccess","tblUsers","UserName = " & UserName & " AND Password = " & Password & ""),"N/A")
' you missed the last closing bracket, other than this the code I provided is the same as yours..

Anyway, the UserName and Password are definitely strings, hence they should be surrounded with single quotes.. So the DLookUp changes as..

UserAccess = Nz(DLookUp("UserAccess","tblUsers","UserName = [SIZE=4][B][COLOR=Red]'[/COLOR][/B][/SIZE]" &  UserName & "[SIZE=4][B][COLOR=Red]'[/COLOR][/B][/SIZE] AND Password = [SIZE=4][B][COLOR=Red]'[/COLOR][/B][/SIZE]" & Password & "[SIZE=4][B][COLOR=Red]'[/COLOR][/B][/SIZE]"),"N/A"[B][COLOR=Red])
[/COLOR][/B]

EDIT: I found the typo, it says password =username, which was right.. Sorry about that..

 

[Charlottew14]

Hi,

Thank you so much everyone, I think I've finally got it working

 

[pr2-eugin]

Glad to hear Charlotte, Good luck..