Database User Name Displayed on Welcome Form (1 Viewer)

Stoic

Registered User.
Local time
Today, 13:13
Joined
May 15, 2013
Messages
24
Hi,
I have an Assess 2007 database with a user table. This table holds database user names and user passwords an entry permission to the database. I also have a welcome form that activates when user enters the database using either of the user names and user password in the user table. Now, I would like to display the database user name (NOT THE SYSTEM USER NAME] on the welcome form of the user that enters the database.
I do not currently have a code for this.
I will appreciate any assistance.
Thanks
 

billmeye

Access Aficionado
Local time
Today, 09:13
Joined
Feb 20, 2010
Messages
542
Are you using a logon form? Does your welcome form open after the logon form closes? If you create a public variable to can store the username of the user after they choose it and then use this public variable in your welcome form to display the username. If you have not used public variables, what you need to do is create a module and the only line you would need is:

Public stUser As String

Then in the After Update event for your logon form UserName set this to the selected name:

stUser = Me!UserName

And now on your welcome form you can use it to display the UserName.
 

Stoic

Registered User.
Local time
Today, 13:13
Joined
May 15, 2013
Messages
24
Yes I am using a Logon form with the user name and password in a table called tblUser.
What I would like to do is when the Logon form closes, I want to place a field on the welcome form that will show the username of the user from the table that has logon.
Thanks
 

pr2-eugin

Super Moderator
Local time
Today, 13:13
Joined
Nov 30, 2011
Messages
8,494
Hello Stoic, Welcome to AWF.. :)

Instead of closing the Logon form, just make it invisible.. then you can access the information everywhere..
Code:
Private Sub logInButtonName_Click()
   [COLOR=Green] 'your code to authenticate.. 
    'instead of DoCmd.Close acForm, Me.Name[/COLOR]
    Me.Visible = False
End Sub
Then in the Welcome Form you can just have the UnBound Text box control source as..
Code:
=Forms![loginFormName]![userNameControl]
 

Stoic

Registered User.
Local time
Today, 13:13
Joined
May 15, 2013
Messages
24
Thanks for the code but I do not want to the login form to remain opened when I click the login in button. Isn't there a code(sql) that calls the user that logon from the user table? Someone spoke about Environment() but I am stuggling with coding it.
Any idea?
Thanks
 

pr2-eugin

Super Moderator
Local time
Today, 13:13
Joined
Nov 30, 2011
Messages
8,494
Stoic Environ Variables are not Database oriented.. They depend on System information.. Try these in your immediate window, you will understand what I mean..
Code:
? Environ("computername")
? Environ("username")
? Environ("userprofile")
Have you tried the code? Or is it just a straight forward 'I want it to close' ?!? Many people would have the Form Invisible.. I for one have in my applications Login forms invisible instead of closing them.. The other alternative is to have Public variables declared in a common module... Assign the Public variable the value of the USER ID, in the Login authentication.. Then you can use that variable anywhere in the application..
 

Stoic

Registered User.
Local time
Today, 13:13
Joined
May 15, 2013
Messages
24
Thanks again, maybe the public variable would word. This it what I have at the back of the login button:
Private Sub cmdLogin_Click()
Dim myDB As DAO.Database
Dim myQDEF As DAO.QueryDef
Dim myRST As DAO.Recordset
Dim strSQL As String
blnPasswordOK = False
Me.txtAttempts.Enabled = False
If IsNull(Me.txtLoginID) Or Me.txtLoginID = "" Then
MsgBox "Enter user ID...", vbInformation
Me.txtLoginID.SetFocus
Exit Sub
End If
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "Enter password...", vbInformation
Me.txtPassword.SetFocus
Exit Sub
End If
strSQL = "SELECT strUserID, strPassword " & _
"FROM tblUsers " & _
"WHERE strUserID=" & """" & Me.txtLoginID & """" & ";"
Set myDB = CurrentDb
' Create a temporary QueryDef object to retrieve
' data from a Microsoft SQL Server database.
Set myQDEF = myDB.CreateQueryDef("")
With myQDEF
.SQL = strSQL
Set myRST = .OpenRecordset()
End With
blnUserFound = Not (myRST.BOF And myRST.EOF)
If blnUserFound Then
blnPasswordOK = (Me.txtPassword = myRST.Fields("strPassword"))
If blnPasswordOK Then
DoCmd.Close
DoCmd.OpenForm "frmProcessingLoginSuccess"
Exit Sub
Else
MsgBox "Incorrect Password..."
Me.txtPassword = ""
Me.txtAttempts = Me.txtAttempts + 1
End If
Else
MsgBox "No such user..."
Me.txtLoginID = ""
Me.txtPassword = ""
Me.txtAttempts = Me.txtAttempts + 1
End If
Me.txtAttempts.Visible = (Me.txtAttempts > 0)
Me.lblAttempts.Visible = (Me.txtAttempts > 0)
Me.Repaint
bln5attempts = (Me.txtAttempts >= 3)
If bln5attempts Then
MsgBox "Too many login attempts!!", vbCritical
blnPasswordOK = True
DoCmd.Quit acQuitSaveAll
Exit Sub
End If
myRST.Close
myQDEF.Close
myDB.Close
End Sub

How can the variable fit in with this.
Thanks
 

billmeye

Access Aficionado
Local time
Today, 09:13
Joined
Feb 20, 2010
Messages
542
Code:
If IsNull(Me.txtLoginID) Or Me.txtLoginID = "" Then
MsgBox "Enter user ID...", vbInformation
Me.txtLoginID.SetFocus
Else
[COLOR="Red"]stUser = Me.txtLoginID[/COLOR]
Exit Sub
 

MSAccessRookie

AWF VIP
Local time
Today, 09:13
Joined
May 2, 2008
Messages
3,428
Thanks for the code but I do not want to the login form to remain opened when I click the login in button. Isn't there a code(sql) that calls the user that logon from the user table? Someone spoke about Environment() but I am stuggling with coding it.
Any idea?
Thanks

Paul presents a good idea as to how to resolve your issue. An alternative would be to create PUBLIC Variables in a COMMON Module that are similar to the one described below to define variables for the UserID and any other information that you need. You can use them the same way you would use any other variable.

PUBLIC CurrentUserID AS String
 

Stoic

Registered User.
Local time
Today, 13:13
Joined
May 15, 2013
Messages
24
Sorry Paul, but here is it:
Code:
Private Sub cmdLogin_Click()
    Dim myDB As DAO.Database
    Dim myQDEF As DAO.QueryDef
    Dim myRST As DAO.Recordset
    Dim strSQL As String
    blnPasswordOK = False

    Me.txtAttempts.Enabled = False

    If IsNull(Me.txtLoginID) Or Me.txtLoginID = "" Then
        MsgBox "Enter user ID...", vbInformation
        Me.txtLoginID.SetFocus
        Exit Sub
    End If

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
        MsgBox "Enter password...", vbInformation
        Me.txtPassword.SetFocus
        Exit Sub
    End If

    strSQL = "SELECT strUserID, strPassword " & _
             "FROM tblUsers " & _
             "WHERE strUserID=" & """" & Me.txtLoginID & """" & ";"

    Set myDB = CurrentDb

    ' Create a temporary QueryDef object to retrieve
    ' data from a Microsoft SQL Server database.
    Set myQDEF = myDB.CreateQueryDef("")
    With myQDEF
        .SQL = strSQL
        Set myRST = .OpenRecordset()
    End With

    blnUserFound = Not (myRST.BOF And myRST.EOF)

    If blnUserFound Then
        blnPasswordOK = (Me.txtPassword = myRST.Fields("strPassword"))
        If blnPasswordOK Then
            DoCmd.Close
            DoCmd.OpenForm "frmProcessingLoginSuccess"
            Exit Sub
        Else
            MsgBox "Incorrect Password..."
            Me.txtPassword = ""
            Me.txtAttempts = Me.txtAttempts + 1
        End If
    Else
        MsgBox "No such user..."
        Me.txtLoginID = ""
        Me.txtPassword = ""
        Me.txtAttempts = Me.txtAttempts + 1
    End If

    Me.txtAttempts.Visible = (Me.txtAttempts > 0)
    Me.lblAttempts.Visible = (Me.txtAttempts > 0)
    Me.Repaint

    bln5attempts = (Me.txtAttempts >= 3)
    If bln5attempts Then
        MsgBox "Too many login attempts!!", vbCritical
        blnPasswordOK = True
        DoCmd.Quit acQuitSaveAll
        Exit Sub
    End If

    myRST.Close
    myQDEF.Close
    myDB.Close

End Sub
 

pr2-eugin

Super Moderator
Local time
Today, 13:13
Joined
Nov 30, 2011
Messages
8,494
So as MSAccessRookie, has suggested create a Public variable in a COMMON module as..
Code:
Public pubStrUserID As String
Then in the login code, just add this..
Code:
[COLOR=Green][B]:[/B][/COLOR]
    strSQL = "SELECT strUserID, strPassword " & _
             "FROM tblUsers " & _
             "WHERE strUserID=" & """" & Me.txtLoginID & """" & ";"
    [COLOR=Red][B]pubStrUserID = Me.txtLoginID[/B][/COLOR]
    Set myDB = CurrentDb
[COLOR=Green][B]:[/B][/COLOR]
 

Stoic

Registered User.
Local time
Today, 13:13
Joined
May 15, 2013
Messages
24
I am not sure, but I am not getting my lines up and running.

Code:
Public strUserID As String
Private Sub txtLoginID_AfterUpdate()
txtLoginID = Me!strUserID
End Sub
=[Forms]![frmLogin]![txtLoginID]
 

pr2-eugin

Super Moderator
Local time
Today, 13:13
Joined
Nov 30, 2011
Messages
8,494
The Public variable need to go in a COMMON Module not the Form Module..

Form Module - Is associated with the Form, and the scope of any variables, even if it is declared as Public will be only till the end of the Form's Lifetime..

Common Module - Is associated with the Application, and scope of any (public) variables and (public) functions in the common module remains until the application is closed..

Why do you have this line of code?
Code:
=Forms!frmLogin!txtLoginID
and that too outside a function??
 

Stoic

Registered User.
Local time
Today, 13:13
Joined
May 15, 2013
Messages
24
Ok, I have added the code: pubStrUserID = Me.txtLoginID
No on the welcome form, whats the code for the text box on the welcome form that will show the name the of the current user?
Thanks.
 

pr2-eugin

Super Moderator
Local time
Today, 13:13
Joined
Nov 30, 2011
Messages
8,494
In the Welcome form's current or Load property, use it like..
Code:
Private Sub Form_Current()
    Me.yourUserNameTextBox = pubStrUserID
End Sub
 

Stoic

Registered User.
Local time
Today, 13:13
Joined
May 15, 2013
Messages
24
Thank you so much Paul. I am sorry, I had to put you through this, but I have got exactly what I wanted. I am truly grateful.
Thanks again
 

pr2-eugin

Super Moderator
Local time
Today, 13:13
Joined
Nov 30, 2011
Messages
8,494
Thank you so much Paul. I am sorry, I had to put you through this, but I have got exactly what I wanted. I am truly grateful.
Thanks again
That's alright Stoic, at the end of the day all that matters is you have solved your problem.. You should see some of my questions, that I asked when I started on this forum.. Hilarious.. Compared to all that, you are definitely not bad.. ;)

Just a small word of suggestion you are the only person who can make the final call on how to do things, but just remember this - "There are many roads that lead to Rome".. :)

Good Luck !!
 

Stoic

Registered User.
Local time
Today, 13:13
Joined
May 15, 2013
Messages
24
I am grateful, and sure that works well for me. Thanks again
 

Stoic

Registered User.
Local time
Today, 13:13
Joined
May 15, 2013
Messages
24
Hi Paul, I am trusting that all is well; I have been doing well with my database but something else came up that I thought to seek your advice on.
1. Instead of the username appearing on the form after login success, I would like to use the actual name of the person that have entered the database. That means in my tblUser table, I already have username, password, user actual name, position, ect. Is it than possible to replace the lookup colom [username] with the actual name colom?

2. I would also like to include in my tables if user X is entering data, a colom should show the username of user X against that data with entry date in another colom.

I will appreciate your assistance in this please.
Oscar
 

Users who are viewing this thread

Top Bottom