Multiple Logins

[Charlottew14]

Hello,

My previous query was solved brilliantly on here but unfortunately now I have another!

My database currently has three logins, one for "research", one for "admin" and one for "service", and depending on which is logged in, a different form opens next.

I'm now looking to expand this to have individual logins to increase security, and need 5 in the "research", 2 in "service and 1 in "admin".

I have a table of username, password and usertype to get this information, but for some reason at the moment it is only letting three of my logins work - one for each category.

How can I make it so that all 8 users have individual usernames/passwords and the relevant form opens up after?!

Thank you!!

 

[Severin]

..we would need to see how it works now... can you upload a version or show us the login vba code?

 

[Charlottew14]

Hi,

I'm a total novice when it comes to code, and a colleague helped me out with this! The code I'm using is:

Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(txtUsername) Or txtUsername = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
txtUsername.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(txtPassword) Or txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblUserLogin to see if this
'matches value chosen in combo box

'StrUserName = TxtUsername

If txtPassword = DLookup("[Password]", "tblUserLogin", "[UserName]='" & txtUsername & "'") Then

'Check User Type

If txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Research'") Then

'Close logon form and open The Research Form

Me.Visible = False
DoCmd.OpenForm "frmCountry"

ElseIf txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Service'") Then

'Close logon form and open The Service Form

Me.Visible = False
DoCmd.OpenForm "frmCSCountryUpdates"


ElseIf txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Service'") Then

'Close logon form and open The Service Form

Me.Visible = False
DoCmd.OpenForm "frmCSCountryUpdates"


ElseIf txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Admin'") Then

'Close logon form and open The Admin Form

Me.Visible = False
DoCmd.OpenForm "frmAdmin"


End If

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
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 admin.", vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub



I don't know if there is a better code to use or not. Any help would be appreciated!!

 

[AusDBGuy]

Hi,
That would add an extra level of complexity as I believe the correct way to do that would be to split the the single table into two:

table: tblUsers
UserID
UserName
Password
DeptID

table: tblDepartments
DeptID
DeptName (EG Research)

When you created a new user you would then have to assign them a department which you could do with a combo box looking up the name of the department instead of the DeptID. You would do that with a query.

It would be easier then to use a combo box to list the usernames. The combo box would have at least 3 columns without the password: UserID, Username, DeptID. You can set the first column with to 0 so it will not show in the combo box.

When the user clicked the login button (after checking for the correct password of course) you could use an a CASE statement to open the correct form. EG

'Column 2 is actually the 3rd column in the combo box because the index starts at 0
Select CASE cboUser.Column(2)

Case 1 'Research Dept

[INDENT]docmd.openform "Research"[/INDENT]

Case 2 'Admin Dept

[INDENT]docmd.openform "Admin"[/INDENT]

Case 3 'Service Dept
[INDENT]docmd.openform "Service"[/INDENT]

I'm less familiar with managing users and passwords but maybe that helps. Perhaps someone else has an easier way?

Forgot to mention you would need to join the two tables using the DeptID in both tables.

 

[Severin]

it's these onces that's wrong:

If txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Research'") Then

The DLookUp will find the first person of that UserType..

 

[Severin]

if you just want to use what you got then:

If txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Research'") Then

would be:

If txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Research' AND [UserName]='" & txtUsername & "'") Then

 

[AusDBGuy]

I beleive Dlookup is not case sensitive either if looking up passwords. I use a binary string compare which allows for case sensitivity

 

[Severin]

..yes that's true if you want case-sensitivity.

Regarding your code then this part:

If txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Research'") Then
'Close logon form and open The Research Form
Me.Visible = False
DoCmd.OpenForm "frmCountry"
ElseIf txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Service'") Then
'Close logon form and open The Service Form
Me.Visible = False
DoCmd.OpenForm "frmCSCountryUpdates"
 
ElseIf txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Service'") Then
'Close logon form and open The Service Form
Me.Visible = False
DoCmd.OpenForm "frmCSCountryUpdates"
 
ElseIf txtUsername = DLookup("[Username]", "tblUserLogin", "[UserType]='Admin'") Then
'Close logon form and open The Admin Form
Me.Visible = False
DoCmd.OpenForm "frmAdmin"
 
End If

you could change into:

select case dlookup("[usertype]","tblUserLogin", "[UserName]='" & txtUsername & "'") 
me.visible = false
case 'Research'
DoCmd.OpenForm "frmCountry"
case 'Service'
DoCmd.OpenForm "frmCSCountryUpdates"
case 'Admin'
DoCmd.OpenForm "frmAdmin"
end select

 

[Charlottew14]

Thank you for your help, but I'm struggling to understand it all!

I can't seem to make the existing code work, so I'm trying AusDBGuy's suggestion of splitting the table into users and departments.

 

[Charlottew14]

If I'm using the CASE statement, what do I need to use to check usernames and passwords?

Thanks!

 

[AusDBGuy]

If your not worried about case-sensitivity,your using a combo box to list your usernames, and the combo box has the following columns as suggested above:

UserID
Username
DeptID

then you could use something like:

 If txtPassword <> DLookup("[Password]", "tblUsers", "[UserID]=" & cboUser.Column(0)) Then
[INDENT] MsgBox "Wrong Password"[/INDENT]
[INDENT]txtPassword.setfocus[/INDENT]
Else


Select CASE cboUser.Column(2)

[INDENT]Case 1 'Research Dept[/INDENT]
[INDENT][INDENT]docmd.openform "Research"[/INDENT][/INDENT]
[INDENT]Case 2 'Admin Dept[/INDENT]
[INDENT][INDENT]docmd.openform "Admin"[/INDENT][/INDENT]
[INDENT]Case 3 'Service Dept[/INDENT]
[INDENT][INDENT]docmd.openform "Service"[/INDENT][/INDENT]
End Select
End If

Hope that helps

 

[Charlottew14]

For some reason it still doesn't work! I don't get an error message or anything, nothing happens when I click the button.

 

[AusDBGuy]

Can you post the code your using.

 

[Charlottew14]

To be honest, I'm a total novice and the coding is going completely over my head!

The code I've got is this, but I doubt its right!


Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(txtUsername) Or txtUsername = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
txtUsername.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(txtPassword) Or txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
txtPassword.SetFocus
Exit Sub
End If


'Check value of password in tblUserLogin to see if this
'matches value chosen in combo box

'StrUserName = TxtUsername

If txtPassword = DLookup("[Password]", "tblUserLogin", "[Usernme]='" & txtUsername & "'") Then



Select Case cboUser.Column(2)
Case 1 'Research Dept
DoCmd.OpenForm "frmCountry"
Case 2 'Admin Dept
DoCmd.OpenForm "frmAdmin"
Case 3 'Service Dept
DoCmd.OpenForm "frmCSCountryUpdates"
End Select
End If

 

[AusDBGuy]

I've had a quick look at your code and there is some errors and inconsistencies with naming conventions. Can you post a copy of your Database? It would be easier to apply the changes directly to the DB and give it back to you so you can see the difference.
Cheers