Opening form based on users logging group

[Reghita]

Hi All

I have developed a login form for one of the database to control the access of users who can use the database. and this is what i am using to check the UserName and Password.

Set Curdb = CurrentDb() 'If your table and form are in same database other wise change it

SQLStmt = "SELECT * FROM [tblSecurity] WHERE [UserName] = '" & Trim("" & Me![txtUserName]) & "'"
SQLStmt = SQLStmt & " AND [Password] = '" & Trim("" & Me![txtPassword]) & "'"
Set SecTB = Curdb.OpenRecordset(SQLStmt, DB_OPEN_DYNASET)
If SecTB.EOF Then
MsgBox "Logon ID or Password not found!", 16, "Incorrect Logon Information"
SecTB.Close
Exit Sub
End If

SecTB.Close
DoCmd.OpenForm "frmSearch"
DoCmd.Close A_FORM, "frmLogIn"
Forms![frmSearch].SetFocus

i have developed two type of same forms one is a read only form and other one is a full access form where the user can add, delete, update information.

i want the forms to open based on what group they belong to

Eg. Username Password Group
David dav12 Read Only
Alex al60 Admin (Full Access)

if David logs in then it should open frmROSearch, (RO = Read Only) and if Alex logs in then should open frmSearch

so what do i need to do to direct the user to particular form based on what group they belong to.


Thanks Heaps
R

 

[FancyPrairie]

You can use the same form, simply set the AllowEdits property.

The following will do this for you (put the following line of code in the OnOpen event of the form). If the user is a member of the Admin group, they have full rights, else they don't.

Private Sub Form_Open(Cancel As Integer)

Me.AllowEdits = MemberOfGroup("Admin&quot

End Sub

This function will determine if a user is a member of a given group.

Function MemberOfGroup(strGroupName As String, Optional varCurrentUser As Variant) As Boolean

'********************************
'* Declaration Specifications *
'********************************

Dim wsp As Workspace
Dim grp As Group
Dim usr As User

Dim i As Integer
Dim bolAnswer As Boolean

Dim strCurrentUser As String

'****************
'* Initialize *
'****************

On Error GoTo ErrHandler

bolAnswer = False

If (IsMissing(varCurrentUser)) Then
strCurrentUser = CurrentUser
Else
strCurrentUser = CStr(varCurrentUser)
End If

Set wsp = DBEngine.Workspaces(0)
Set grp = wsp.Groups(strGroupName)
Set usr = wsp.Users(strCurrentUser)

'******************************************************************
'* Loop to determine if the User is part of the group specified *
'******************************************************************

For i = 0 To grp.Users.Count - 1
If grp.Users(i).Name = usr.Name Then
bolAnswer = True
GoTo ExitProcedure
End If
Next i

wsp.Close

'********************
'* Exit Procedure *
'********************

ExitProcedure:

MemberOfGroup = bolAnswer

Exit Function

'****************************
'* Error Recovery Section *
'****************************

ErrHandler:

If Err.Number = 3265 Then
MsgBox UCase(strGroupName) & " isn't a valid group name", vbExclamation
ElseIf Err.Number = 3029 Then
MsgBox "The account used to create the workspace does not exist"
Else
MsgBox Err.Number & ": " & Err.Description
End If

wsp.Close
Resume ExitProcedure

End Function

 

[RickSpr]

First, add the Group column to tblSecurity. (Actually, if you have the option, why not put the startup form name in there instead, or in addition? It would give you greater flexibility.)

Then change your code to this:
Dim strStartupForm As String

Set Curdb = CurrentDb() 'If your table and form are in same database other wise change it

SQLStmt = "SELECT * FROM [tblSecurity] WHERE [UserName] = '" & Trim("" & Me![txtUserName]) & "'"
SQLStmt = SQLStmt & " AND [Password] = '" & Trim("" & Me![txtPassword]) & "'"
Set SecTB = Curdb.OpenRecordset(SQLStmt, DB_OPEN_DYNASET)
If SecTB.EOF Then
MsgBox "Logon ID or Password not found!", 16, "Incorrect Logon Information"
SecTB.Close
Exit Sub
End If

' Use this if you put the Group in tblSecurity
Select Case Nz(SecTB![Group])
Case "Read Only"
strStartupForm = "frmROSearch"
Case "Admin"
strStartupForm = "frmSearch"
Case Else
strStartupForm = ""
End Case
' Or use this if you put the form name in tblSecurity
strStartupForm = Nz(SecTB![StartupForm])

SecTB.Close
On Error GoTo InvalidForm
DoCmd.OpenForm strStartupForm
DoCmd.Close A_FORM, "frmLogIn"
Forms(strStartupForm).SetFocus ' this is optional
Exit Sub
InvalidForm:
MsgBox "You have been assigned to an unknown security group. " _
& "Please contact your security administrator.", _
vbExclamation, "Security Error"
Application.Quit
Rick Sprague

 

[Reghita]

Hi FancyPrairie and Rick Sprague

Thank you so much for helping me out.

i am using the method Rick Sprague described. when i type the username and password it is taking me to the define form for that particular user but i am getting the InvalidForm message where it says to contact the security Admin and close the application.

I am not sure why i am getting that message even tho it's taking me to the right form.

can you please let me know what i am doing wrong

this is how my code looks like now:

Private Sub CMDok_Click()

Dim strStartupForm As String

Set Curdb = CurrentDb() 'If your table and form are in same database other wise change it

SQLStmt = "SELECT * FROM [tblSecurity] WHERE [UserName] = '" & Trim("" & Me![txtUserName]) & "'"
SQLStmt = SQLStmt & " AND [Password] = '" & Trim("" & Me![txtPassword]) & "'"
Set SecTB = Curdb.OpenRecordset(SQLStmt, DB_OPEN_DYNASET)
If SecTB.EOF Then
MsgBox "Logon ID or Password not found!", 16, "Incorrect Logon Information"
SecTB.Close
Exit Sub
End If

strStartupForm = Nz(SecTB![StartUpForm])

SecTB.Close
On Error GoTo InvalidForm
DoCmd.OpenForm strStartupForm
DoCmd.Close A_FORM, "frmLogIn"
Forms![strStartupForm].SetFocus
Exit Sub

InvalidForm:
MsgBox "You have been assigned to an unknown security group. " _
& "Please contact your security administrator.", _
vbExclamation, "Security Error"


Thankyou somuch
R

 

[Reghita]

Thanks Guys

I worked out why i was getting that message and it's working fine. i just needed to remove the
Forms(strStartupForm).SetFocus from the Code

Thanks again it really helped me

i got an another question is there any way that i can use the same form "frmSearch" but authorise different level of security like "Admin" and "Read only"

P.S: without using Access buildin Security

R

 

[RickSpr]

Oops! I got the syntax wrong on the SetFocus statement. It should be:
Forms(strStartupForm).SetFocus

The On Error statement turned on error capture, so we could catch an error if you had an invalid form name in tblSecurity. I didn't bother to turn it off after the OpenForm, which is where I expected the error. Since I didn't turn it off, the error in the SetFocus line activated the error handler.

You could turn off the error handling by putting this after the OpenForm statement:
On Error GoTo 0
However, since this is a security form, it might be better to catch any unexpected errors and simply exit, rather than give the user an Access error message which they can dismiss, and then either get into places they shouldn't be allowed, or not be able to do anything but exit Access anyway.

This code appears to have been written in Access version 2, so I assume you borrowed it, and maybe you don't really know VBA very well. If that's true, you might want to think about having somebody who does know VBA well improve the error handling throughout the application. What I gave you is very basic error handling, not well developed. A secure application really needs to have more rigorous error handling. Otherwise, runtime errors could interrupt your code's control and give secured data to unauthorized users. Rick Sprague