Login Forms

[Eliasgitau]

Dear Learned Friends,
I have a form to make the login to the application, the username is stored in a public variable.
Logging on the application will send the user to the menu form. The scenerio is. I have engineers who create records whom i would like them to be viewing their own jobs only and also have other users who should view all jobs and update various pages on the form. I have been using the workgroup security to control all this with the help of currentuser(). Since now I have several database it has become difficult to be switching from one security file to the other for the users. With memory variable i have been able to track (debug) the user name upto main menu(Swithboard). If i select any option on the menu it does not call the option,but if i use the workgroup security file with the help of currentuser() the operations are okay. Please find my codes below.

***Global module
Option Compare Database
Global Wuser As String
Global WuserID As Long
Option Explicit

Public Function Init_Globals()
WuserID = 0
Wuser = ""
End Function

Public Function Get_Global(gbl_parm)
Select Case gbl_parm
Case "WuserID"
Get_Global = WuserID
End Select
End Function

**** Form Login
Option Compare Database
Private intLogonAttempts As Integer

Private Sub Form_Open(Cancel As Integer)
'On open set focus to combo box
Me.cboEmployee.SetFocus
Init_Globals
End Sub

Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
WuserID = Me.cboEmployee
Wuser = Me.cboEmployee.Column(1)
Me.txtPassword.SetFocus
End Sub

Private Sub cmdLogin_Click()
box
If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployee.SetFocus
Exit Sub
End If
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
Wuser = Me.cboEmployee.Column(1)
DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "Switchboard", , , , , , Wuser
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If
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

*****Form Startup
Public Sub Form_Current()
Wuser = Get_Global(Wuser)
If Wuser = "RN" Then
DoCmd.OpenForm "JobSystemcreation", , , "projectcoordinator" = Wuser
ElseIf Wuser = "DS" Then
DoCmd.OpenForm "JobSystemcreation"
ElseIf Wuser = "YS" Then
DoCmd.OpenForm "JobSystemcreation"
ElseIf Wuser = "ADMIN" Then
DoCmd.OpenForm "JobSystemcreation"
ElseIf Wuser = "MASTER" Then
DoCmd.OpenForm "JobSystemcreation"
ElseIf Wuser = "NJOROGE" Then
DoCmd.OpenForm "JobSystemcreation"
End If
End Sub

Private Sub Form_Load()
DoCmd.Close
End Sub

Thanks In Advance
Elias Gitau

 

[jrbarnett]

If you are using different workgroup files for different applications, this will explain why your security doesn't transfer between users. Internally, security isn't based on user IDs - its internal security IDs.

The easiest way around this is to code your own security system: add a new table tblUsers:

Username text (20) Primary key
Password Text (20)
ViewAll Boolean Not Null Default False

Now, create a form to add users - by adding a new row to that data. Ideally you want to reuse one of the password scrambling routines on this site so its not stored as plain text.
Then, create a login form - textboxes to enter username and password, with the password input mask on the Password textbox. Have an OK button to verify the username/password - something like:

Private Sub cmdOK_Click
  If DCount ("*", "tblUsers", "Username='" & Me.txtUsername & "' AND Password='" & me.txtPassword & "'") =1 Then
    ' correct user id/password combination, set variable and open switchboard
    WUser = me.txtUsername
    DoCmd.OpenForm "Switchboard"
    DoCmd.Close acForm, Me.Name ' Close current login form
  Else
    MsgBox "Incorrect username/Password combination"
  End If
End If

You can verify the IsAdmin access in the switchboard to determine if they are entitled to access all records (and also of course the administration screens to set up new accounts or reset forgotten passwords).

John

 

[Eliasgitau]

Thanks John,
To explian a bit:-
1.) I want to move from using different workgroup files for different applications.
2.) Now Ihave login forms. It is from this form am capturing username to public variable (Wuser) as shown in the code above under form login. the public variable is being intialised in global module named above.

NB:
Before changing to login Forms I was using the below code to open the form with user's records only whereby the user was captured from currentuser() function, but now am unable when i capture wuser from public variable. Also note when I debug the code below Wuser varible contains reference "RN". Please why is it not opening the form.

If Wuser = "RN" Then
DoCmd.OpenForm "JobSystemcreation", , , "projectcoordinator" = Wuser
endif

I hope this explains my situation properly.

Regards
Elias Gitau

 

[jrbarnett]

WUser is a string variable (text data type) in the database.
You need to tell VBA to treat it as such when opening the form, thus:

DoCmd.OpenForm "JobSystemcreation", , , "projectcoordinator='" & Wuser & "'"

Secondly, I've just noticed a mistake in my previous code: the last line "End If" should read "End Sub"

John