Change switchboard for each user-group ...

[m.cringle]

Hi again!

I would like to be able to display a slightly different switchboard for
each of my user-groups at logon. Currently, each user sees the same
menu but will receive an error message if they try to open something
which permissions have not been granted.

Can anyone tell me the easiest way to do this?

Much appreciated as always

 

[Guest]

Create separate switchboards for each user group, and the switchboard that
opens will depend on which group they belong to at login.

 

[m.cringle]

Create separate switchboards for each user group, and the switchboard that
opens will depend on which group they belong to at login.

Do you know how to assign each switcboard to a specific user group?

Do I restrict access to the others in permissions and the remaining
switchboard with access permissions will launch?

 

[Guest]

This is the way I was taught to do it a school, don't know if it is what you
are looking for or not. Create a new module, use this code, you will have to
change the names of the forms and the user types to suit your need. No
guarantees but it works fine forme. I am still some what new at this and if
possible would like to contribute back to this forum.

Good luck

' Name: procLogin
' Purpose: Validate username/ password
' Parameters: Username and password entered as a string
' Called by: frmLogin

Sub procLogin(UserName As String, Pwd As String)

' 1.0 variable for a database connection
Dim dbsConnection As Database

' 1.1 variable for a recordset object
Dim rsUser As Recordset
Dim sqlString As String
Dim UserType As String

' 1.2 Connect to database by calling a module function.
Set dbsConnection = procConnectToDatabase()

' 1.3 Validate UserName Password UserType
sqlString = "SELECT tblUser.UserID, tblUser.UserName, tblUser.Password,
tblUser.UserTypeID, tblUserType.UserType" & _
" FROM tblUserType INNER JOIN tblUser ON tblUserType.UserTypeID =
tblUser.UserTypeID" & _
" WHERE (((tblUser.UserName)='" & UserName & "') AND ((tblUser.Password)='"
& Pwd & "'));"

Set rsUser = dbsConnection.OpenRecordset(sqlString)

' 1.4 When finished close the recordset object
' if at eof(End OF File) didnt find a match
If rsUser.EOF Then
procDisplayMessage ("You don't seem to be in our system")
Forms!frmLogin!txtUsername.SetFocus

' 1.5 If user is found open one of the appropriate forms
Else
'Close Login Form
DoCmd.Close

If rsUser!UserType = "System Manager" Then
DoCmd.OpenForm "frmSwitchboardSM", , , , , , rsUser!UserTypeID
Else
If rsUser!UserType = "Employee" Then
DoCmd.OpenForm "frmSwitchboardSM", , , , , , rsUser!UserTypeID
Else
If rsUser!UserType = "Customer" Then
DoCmd.OpenForm "frmSwitchboardCustomer", , , , , , rsUser!UserTypeID
End If
End If
End If

End If

' 1.6 when finished close record set and close database connection
rsUser.Close
dbsConnection.Close
Exit Sub

End Sub

 

[m.cringle]

Cheers Coach - will give it a go!

This is the way I was taught to do it a school, don't know if it is what you
are looking for or not. Create a new module, use this code, you will have to
change the names of the forms and the user types to suit your need. No
guarantees but it works fine forme. I am still some what new at this and if
possible would like to contribute back to this forum.

Good luck

' Name: procLogin
' Purpose: Validate username/ password
' Parameters: Username and password entered as a string
' Called by: frmLogin

Sub procLogin(UserName As String, Pwd As String)

' 1.0 variable for a database connection
Dim dbsConnection As Database

' 1.1 variable for a recordset object
Dim rsUser As Recordset
Dim sqlString As String
Dim UserType As String

' 1.2 Connect to database by calling a module function.
Set dbsConnection = procConnectToDatabase()

' 1.3 Validate UserName Password UserType
sqlString = "SELECT tblUser.UserID, tblUser.UserName, tblUser.Password,
tblUser.UserTypeID, tblUserType.UserType" & _
" FROM tblUserType INNER JOIN tblUser ON tblUserType.UserTypeID =
tblUser.UserTypeID" & _
" WHERE (((tblUser.UserName)='" & UserName & "') AND ((tblUser.Password)='"
& Pwd & "'));"

Set rsUser = dbsConnection.OpenRecordset(sqlString)

' 1.4 When finished close the recordset object
' if at eof(End OF File) didnt find a match
If rsUser.EOF Then
procDisplayMessage ("You don't seem to be in our system")
Forms!frmLogin!txtUsername.SetFocus

' 1.5 If user is found open one of the appropriate forms
Else
'Close Login Form
DoCmd.Close

If rsUser!UserType = "System Manager" Then
DoCmd.OpenForm "frmSwitchboardSM", , , , , , rsUser!UserTypeID
Else
If rsUser!UserType = "Employee" Then
DoCmd.OpenForm "frmSwitchboardSM", , , , , , rsUser!UserTypeID
Else
If rsUser!UserType = "Customer" Then
DoCmd.OpenForm "frmSwitchboardCustomer", , , , , , rsUser!UserTypeID
End If
End If
End If

End If

' 1.6 when finished close record set and close database connection
rsUser.Close
dbsConnection.Close
Exit Sub

End Sub

 

[Joan Wild]

There is a sample function in the security FAQ you can use to determine if a
user is a member of a group
http://support.microsoft.com/?id=207793

Instead of separate 'switchboard' forms you could have one and simply hide
buttons that a particular group is not supposed to see.

If faq_IsUserInGroup("EnterData",CurrentUser) then
Me.cmdWhatever.Visible = False
Me.cmdSomething.Visible = False
Else
Me.cmdWhatever.Visible = True
Me.cmdSomething.Visible = True
End If

 

[m.cringle]

Hi again - just a couple of Q's ..

1 - I take it that I will need to create another table which holds all
the user names, types and passwords?

It would be difficult to keep the password table updated because I wont
know everyones password - will it work if I modify code to only need
the user name and corresponding user group in the table?

2 - Also, what will cause this event to run? Do I have to set it some
way to run at start-up?

Thanks again

 

[m.cringle]

Sorry, another Q

Is this compatable with my already existing user-level security? I
notice that the code seems to point to another login form.

I was hoping to be able to either automatically choose the
corresponding switchboard during log-in or hide certain options from
view for certain users
- Thought there might have been an easier option

 

[m.cringle]

Exactamondo! Just what I needed

Where about would I paste this code for it to run when the database
opens though?

Cheers

 

[Joan Wild]

If you are going with a single form you can put it in the open event for the
form.

If you are going with separate forms, then you can have a hidden form that
opens on startup, and put the code in the open event for this form,
adjusting the code to open the appropriate form, rather than hiding buttons.

 

[m.cringle]

If you are going with a single form you can put it in the open event for the
form.

If you are going with separate forms, then you can have a hidden form that
opens on startup, and put the code in the open event for this form,
adjusting the code to open the appropriate form, rather than hiding buttons.

Hi Joan

I have managed to hide certain buttons when specific users logon
however, I think it would look much better if I could have a seperate
form for each user group.

I altered the code to open a specified form for a user group by adding
the code to the OnOpen command on the switchboard. However, obviously
the default switchboard opens also. Can you tell me how I'm supposed to
create an invisible form which starts up on login? Normally you have
the visible (yes/No) option but in this case I don't see it?

Will I have to delete my switchboard and set up a series of forms as
replacements and make the code manually?

Thanks for your help - its really appreciated

 

[Joan Wild]

The individual 'switchboard' forms don't need any code with regard to the
user's group membership.

I would create different forms for each group - just create an unbound
form - it's much more flexible than the switchboard manager in Access. You
can add buttons, comboboxes, textboxes, etc. as needed.

Create a separate form for the startup - set this form to open on startup.
Put the code in it to determine the current user's group and open the
appropriate menu form.

To open this hidden, put Me.Visible = False in the open event before the
code that determine's the group membership.

 

[m.cringle]

Please ignore the last message - I have figured it out

Thanks anyways!