Display User Name from Login form

[drschlong]

Hi all,

I have a basic table called tblUserTable which holds the following fields "UserName" and "Password". I then have a Login form called "frmLogin" which has the fields "UserName and "Password". On this form there is a button which has the following code in the On Click property.

Private Sub cmdOK_Click()
Dim strPassword As String
Static Tries As Integer
'Check to see if password is entered
If IsNull(Password) = True Then
MsgBox "Please enter your Password", , "No Password Entered"
Password.SetFocus
Exit Sub
Else
'Gets the Password stored in tblUserTable
strPassword = DLookup("Password", "tblUsertable", "[UserName]=forms!frmLogin!cboUserName")
End If
If Password = strPassword Then
DoCmd.Close
DoCmd.OpenForm ("frmMainPMS")
Else
MsgBox "The Password you have entered is incorrect.", , "Please enter it again."
Tries = Tries + 1
Password.SetFocus
End If
If Tries > 3 Then
MsgBox "You do not have access to this system" & Chr(13) & Chr(13) & "Please Contact your administrator", vbCritical, "Restricted Access!"
DoCmd.Close
End If
End Sub

What I want to be able to do is once the user has logged in succesfully there needs to be a field on the form "frmMainPMS" which shows the name of the user (UserName from tblUsername)logged in.

Any help anybody could give me with this would be much appreciated.

Thanks,

Steven. C.

 

[Mile-O]

Are you allowing the person to pick their name from a drop-down box or something?

If so, it's probably better to store it in a public variable.

That way, on another form, you'll still hold the name and can use it on another form.

Also, in the code:

strPassword = DLookup("Password", "tblUsertable", "[UserName]=forms!frmLogin!cboUserName")

I'm not sure if this would work, but this would:

strPassword = DLookup("Password", "tblUsertable", "[UserName] = '" & forms!frmLogin!cboUserName & "'")

 

[ColinEssex]

Or alternatively if your Db is on a network you can pick up the username from their username that they use to get into the network.

Mile-O-Phile's way is just as good though and probably easier.

Post again if you need any more help


Col

 

[drschlong]

I am letting them pick the username from a drop down.

Where do I declare the public variable and then how do I display the value of that variable in a field on a form.

Sorry if this sounds as though I'm being a bit dense, but I don't really know a lot about coding, only what I pick up as I go along.

Thanks for the help so far.

Steven.

 

[Mile-O]

I actually use a mixture of both ways.

I get the user's system ID and use it to lookup their name on a headcount table that's linked from another database.

The only problem I found was that when it came to logging out of the database some people would use the X in the top-right corner or Task Manager so I had to disable the X button and create a little system manager database that links to all my databases so I can monitor who is in which one at any time.

At least now, when they had to phone to be reset I could investigate who was using Task Manager to close the database down and tell them off for it.

Now, with time, people are using the Logout button that they were originally provided with.

 

[Mile-O]

To declare a public variable, rather than use

Dim strUserName as String

put

Public strUserName as String

But, declare this variable in a module.

 

[drschlong]

Ok, so I have created a module and left it named as Module1 for the moment.

The code inside the Module looks like this:

Option Compare Database
Option Explicit
Public strUserName As String

Is this right, if so how do I then get the field on my other form to display this value.

 

[Mile-O]

On your login screen - on the dropdown's AfterUpdate() event you can put this code where cboSelectUser is replaced by the name of your combobox.

strUserName = cboSelectUser

On the other form, I'm not too clear what you mean by field with respect to a form. If you have an object, such as a textbox, with its ControlSource set to a field in a query or a table then it's just a case of assigning the value in the forms Load() event.

Private Sub Form_Load()
    
    txtCurrentUser = strUserName    
    
End Sub

where txtCurrentUser is the name of your textbox.

 

[drschlong]

Mile-o-Phile,

Thanks for all your help, got it working now.

Couldn't have done it without you.

Cheers mate,

Steven. C.

 

[Robert Dunstan]

Hi there

I have a very similar situation whereby I store the login name and permission in public variables. On my login form the user selects there name for a combo box, then enters their password and then clicks OK. The code behind cmdOK is as follows:

Private Sub cmdOK_Click()

' Check to password entered is correct

If IsNull(txtPassword) = True Then
strMessage = "Please enter your password"
style = vbCritical
strTitle = "No Password Entered"

MsgBox strMessage, style, strTitle
txtPassword.SetFocus
Exit Sub
Else

' Get the password and permission level
strPassword = DLookup("[Password]", "tblEmployees", "[EmployeeID] = Forms!frmLogin!cboUserName")
Permission = DLookup("[Level]", "tblEmployees", "[EmployeeID] = Forms!frmLogin!cboUserName")
userRights (Permission) 'Call the userRights public sub and store the permission value
LoggedInAs = DLookup("[EmployeeName]", "tblEmployees", "[EmployeeID] = Forms!frmLogin!cboUserName")

End If

If txtPassword <> strPassword Then
strMessage = "The Password you have entered is incorrect."
strMessage = strMessage & vbCr & vbCr
strMessage = strMessage & "Please enter it again."
style = vbCritical
strTitle = "Incorrect Password"

MsgBox strMessage, style, strTitle
Tries = Tries + 1
txtPassword.SetFocus

If Tries > 3 Then
DoCmd.Quit
End If
Else 'Set the menu layout

' This section defines the menu layout based on the user's permission level
Dim Menu As Variant
Dim ctl As Variant

Menu = "Global Menu"


Set ctl = CommandBars(Menu).Controls("&Timesheets")
If Permission >= 2 Then
ctl.Enabled = True
Else
ctl.Enabled = False
End If

Set ctl = CommandBars(Menu).Controls("&File")
ctl.Enabled = True

Set ctl = CommandBars(Menu).Controls("&Orders")
If Permission >= 1 Then
ctl.Enabled = True
Else
ctl.Enabled = False
End If

Set ctl = CommandBars(Menu).Controls("&Reports")
If Permission >= 1 Then
ctl.Enabled = True
Else
ctl.Enabled = False
End If

Set ctl = CommandBars(Menu).Controls("System &Administration")
If Permission >= 9 Then
ctl.Enabled = True
Else
ctl.Enabled = False
End If

Set ctl = CommandBars(Menu).Controls("&Suppliers")
If Permission >= 1 Then
ctl.Enabled = True
Else
ctl.Enabled = False
End If


DoCmd.OpenForm "frmMainMenu"
DoCmd.Close acForm, "frmLogin"

End If


End Sub

 

[Kila]

Hi Robert! I have found this post to be very helpful, but I am not incredibly familiar with VB. I am following and adapting your code above pretty well, but I am at a loss to what this line is talking about....

Set ctl = CommandBars(Menu).Controls("&Suppliers")

What is the ctl? Is it a button on the MainMenu form? What/where is the "Commandbars"?

Thanks for your help!

 

[Kila]

Also, what is this UserRights Sub? THe debug is looking for it. Thanks.

userRights (Permission) 'Call the userRights public sub and store the permission value

 

[Robert Dunstan]

Hi Kila

OK to answer your questions:

1 The Set ctl = CommandBars(Menu).Controls("&Suppliers") line basically assigns the name of my menu bar control to the 'ctl' variable. The ctl variable is declared further up on the line Dim ctl as Variant. The lines beneath then test the value contained in the Permission public variable and disables that menu control if the user doesn't have the appropriate permission.

2 For the userRights sub, this is a public sub declared in a module. To do this you need to go to the database window and click on 'Modules'. Create a new module and use this code:

Public Sub userRights(PermissionLevel As Integer)

Permission = PermissionLevel

End Sub

Hopefully I've explained it as clear as muddy water but if you need more help then post again

Rob

 

[Kila]

Thank you. Hopefully I will be able to adapt the rest for my database.

As I said, I am not very familiar with VB. Can you please tell me why it is necessary to set a field as an integer when it is already a number field? I have seen this used a lot.

 

[Robert Dunstan]

Hi Kila

I think you might getting confused with variables. When you set a field in a table to a number data type, you are effectively setting it is an integer i.e. store a numeric. Whereas Variables that are delcared in code using the Dim statement are effectively containers in which you wish to hold something. This container is then held in memory until you want to retrieve whats in it.

There are several different types you can declare in code:

Dim myVar as Integer - holds numerical values
Dim myVar as String - holds non numeric characters e.g. user name
Dim myVar as Control - can be set to a particular control on form or report

 

[Kila]

Thanks. One other question about these basics, if you don't mind. What does Dim stand for?

 

[Kila]

I am working on adapting the code in Access 97. On running the login box, I got...

"Invalid use of Null"

for the line

UserRights (Permission) 'Call the UserRights public sub and store the permission value


What might be causing this? I created a module called basUserRIghts with the information you provided.

Thanks.

 

[Mile-O]

Thanks. One other question about these basics, if you don't mind. What does Dim stand for?

Set the cursor on it while in the Visual Basic Editor and press the F1 key to find out.

 

[Kila]

Well, how handy is THAT?! BG thought of everything!