UserName stamped in the report Footer

[smtazulislam]

Hi, any help will appreciate.
I have multiple users. I would like to do, if user logon and open the report then his/her name is stamped in the report footer. I'm tried it many ways to resolve this problem. Here is show you..

I tried it by TempVars function in Open event.

Private Sub Report_Open(Cancel As Integer)
    Dim strSQL As String
    Dim currentUserName As String
    
    ' Get the current user's name from TempVars
    currentUserName = TempVars!UserName
    
    ' Build the SQL query
    strSQL = "SELECT * FROM Users WHERE UserName='" & currentUserName & "';"
    
    ' Apply the SQL query to the report's RecordSource
    Me.RecordSource = strSQL
End Sub

in form use it:

Private Sub btnPrint_Click()
    Dim currentUserName As String
    currentUserName = GetCurrentUserName() 
    ' Set TempVar with the current username
    TempVars.Add "UserName", currentUserName
    
    ' Open the report
End Sub

Tried it too

Public Function GetCurrentUserName(userID As Long) As String
    Dim currentUserName As String
    Dim rs As DAO.Recordset
    
    ' Get the current user's name from user table
    Set rs = CurrentDb.OpenRecordset("SELECT UserName FROM Users WHERE UserID = " & userID)
    If Not rs.EOF Then
        currentUserName = rs("UserName")
    Else
        currentUserName = "Unknown"
    End If
    rs.Close
    
    GetCurrentUserName = currentUserName
End Function

However, I had tried by Data source. no sense how to add each table sql with Users table.

 

[June7]

Why would you set report RecordSource to Users table? What is this report for?

Expression in textbox can reference TempVar.

=TempVars!UserName

Set TempVar when user logs in and it will remain available for reference until changed or db closed.

currentUserName = GetCurrentUserName() fails because it does not pass UserID parameter expected by the function's userID argument.

 

[Pat Hartman]

Your code doesn't seem to have much to do with your post's subject. I'm going to assume that the subject is what you are trying to do.

Assumptions
--- you want the name of the user who printed this report to be displayed in a control in the footer of the report.
--- somehow, there is a TempVar that holds the UserID - this is where things start to get murky -- I can't tell where this value is coming from so I'm going to ignore the TempVar until you clarify how it is getting populated and what it is populated with - the ID or the Name? Because it should NOT be called UserName if it actually stores UserID. That will just make everyone's head spin.
--- given the UserID, you want to look up the actual user name to use that in the footer rather than the ID value.

Start by trying this as the ControlSource for the control in the Report's footer.

=dLookup("UserName", "User", "UserID = " & Forms!someform!UserID

I have no idea where UserID comes from so you need to clarify. In your login code, you should create two tempvars. One with the ID and the other with the name. That way the lookup only ever happens ONCE when the user logs in. Then to show the user name, the ControlSource would be:

=TempVars!UserName.Value

 

[isladogs]

I've also no idea how the code is meant to know the user name from what you've described.
Are users logging into your app with a user name / password?

If not, you need to grab the user name for when users log into Windows

There are at least 3 methods of getting the user name from Windows. See my article:

Get User Name

This article describes 3 common methods of getting the current user name in Access

www.isladogs.co.uk

I recommend using CreateObject("WScript.Network").UserName

 

[smtazulislam]

I've also no idea how the code is meant to know the user name from what you've described.
Are users logging into your app with a user name / password?

If not, you need to grab the user name for when users log into Windows

There are at least 3 methods of getting the user name from Windows. See my article:

Get User Name

This article describes 3 common methods of getting the current user name in Access

www.isladogs.co.uk

I recommend using CreateObject("WScript.Network").UserName

Thank you very much, I would like who is logon app and stamped his name in the footer.

I have no idea where UserID comes from so you need to clarify. In your login code, you should create two tempvars. One with the ID and the other with the name. That way the lookup only ever happens ONCE when the user logs in. Then to show the user name, the ControlSource would be:

=TempVars!UserName.Value

this is kidding ! I don't believe it. one hours I had tried it many ways to resolve it. now a simply code is work.
Thank you very much. I appreciate you.

 

[smtazulislam]

any idea, how to stamp with "FirstName + LastName" ?

EDIT :
users TABLE data source is like : UserName, FirstName, LastName,

 

[CJ_London]

Do you think

dlookup(“firstname & ‘ ‘ & lastname”,…,

would be worth a try?

 

[smtazulislam]

Do you think

dlookup(“firstname & ‘ ‘ & lastname”,…,

would be worth a try?

=TempVars!FirstName & " " & TempVars!LastName & " " & TempVars!UserName

not work. Only userName display
then tried

=[TempVars]![UserName] & " " & DLookUp("FirstName","Users")

not work

 

[CJ_London]

so not worth thinking about it then

with regards the tempvars method - I presume you have created those values for first and last name

and if you use dlookup you need to include a criteria

and 'not work' does not help us to help you.

 

[Pat Hartman]

Why doesn't UserName contain the name as you want it displayed? Go back and fix that in the query used to populate the TempVars!UserName
PS, you would almost certainly NOT want FirstName + LastName. You would want FirstName & " " & LastName so there is a space between the two names.

Also, the + and the & do not work the same way. Make sure you know which you want in any given situation.

 

[June7]

DLookup() needs criteria to specify which record to pull data from. See example in post 3. Concatenate whatever fields you want to pull from.

[TempVars]![UserName] = DLookUp("firstname & ‘ ‘ & lastname", "Users", "UserID=" & [UserID])

Again, where UserID parameter comes from is unknown. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[smtazulislam]

DLookup() needs criteria to specify which record to pull data from. See example in post 3. Concatenate whatever fields you want to pull from.

[TempVars]![UserName] = DLookUp("firstname & ‘ ‘ & lastname", "Users", "UserID=" & [UserID])

Again, where UserID parameter comes from is unknown. If you want to provide db for analysis, follow instructions at bottom of my post.

Report, how to know what is the userId...

 

[June7]

You tell us how you know who the user is. What does the login procedure look like? All data about user needed for future reference should be retrieved and saved somewhere by that procedure.

Again, probably should provide your db for analysis.

 

[smtazulislam]

here is it...

 

[Pat Hartman]

1. Your code is at least 5 levels deep and is not consistently formatted so it is very difficult to tell what is happening when.

Suggestion:
You can identify several of the errors right off. Just add an Exit sub. So each subsequent error test goes back to level 1 and that will minimize your indenting. I find that people who use ElseIf never indent properly.

2. Having a combo for the user to choose his UserID goes a long way toward defeating the purpose of bothering to have them to log in at all. The user should be required to type his UserID. This is not about convenience. It is about security. With the combo, some unauthorized user has half the information he needs to open the database.

3. Allowing the user to create his own log in is downright silly. That means that you essentially have NO security at all.

4. The simplest way to validate a user's credentials is to use
SomeVariable = dLookup("Full Name", "Users", "UserName= '" & Me.txtUserName & "' AND Password = '" & Me.txtPassword & "'")
If it finds something, the credentials are valid. If it doesn't, they aren't. You should never tell the person logging in that the UserID is ok but the password is bad. Again, it defeats the purpose of logging in.

5. Now we get to the issue of setting the TempVars. The bound column of the combo is the UserName so when you refer to the combo with:

Me.txtUserName

What you get is the "bound" column which in your case is defined as column 1 but what you really want for the TempVars!FullName is the second column SO

TempVars("FullName").Value = Me.txtUserName.Column(1)

.Column(1) refers to the Second column of the RowSorce. The RowSource is a zero based array so the first column is either just the name - Me.txtUserName or Me.txtUserName.Value or Me.txtUserName.Column(0). But any other column can only be referenced by using the Column property.

PS, I changed the name of the TempVar because you really don't want it to be named UserName when in fact, it is holding the Full Name.

Start by simplifying your code. It is overly complicated and too deeply nested. And even though the compiler doesn't care whether the code is rationally formatted or not, humans do need the visual clues that good formatting provides.

 

[smtazulislam]

1. Your code is at least 5 levels deep and is not consistently formatted so it is very difficult to tell what is happening when.

Suggestion:
You can identify several of the errors right off. Just add an Exit sub. So each subsequent error test goes back to level 1 and that will minimize your indenting. I find that people who use ElseIf never indent properly.

2. Having a combo for the user to choose his UserID goes a long way toward defeating the purpose of bothering to have them to log in at all. The user should be required to type his UserID. This is not about convenience. It is about security. With the combo, some unauthorized user has half the information he needs to open the database.

3. Allowing the user to create his own log in is downright silly. That means that you essentially have NO security at all.

4. The simplest way to validate a user's credentials is to use
SomeVariable = dLookup("Full Name", "Users", "UserName= '" & Me.txtUserName & "' AND Password = '" & Me.txtPassword & "'")
If it finds something, the credentials are valid. If it doesn't, they aren't. You should never tell the person logging in that the UserID is ok but the password is bad. Again, it defeats the purpose of logging in.

5. Now we get to the issue of setting the TempVars. The bound column of the combo is the UserName so when you refer to the combo with:

Me.txtUserName

What you get is the "bound" column which in your case is defined as column 1 but what you really want for the TempVars!FullName is the second column SO

TempVars("FullName").Value = Me.txtUserName.Column(1)

.Column(1) refers to the Second column of the RowSorce. The RowSource is a zero based array so the first column is either just the name - Me.txtUserName or Me.txtUserName.Value or Me.txtUserName.Column(0). But any other column can only be referenced by using the Column property.

PS, I changed the name of the TempVar because you really don't want it to be named UserName when in fact, it is holding the Full Name.

Start by simplifying your code. It is overly complicated and too deeply nested. And even though the compiler doesn't care whether the code is rationally formatted or not, humans do need the visual clues that good formatting provides.

My apology delay reply. Thank you very much for your advise . herein some users does not like to type they're user name, the reasons I instead combo.

I given a features to user himself fillup form data details then Administrator will give USERROLES only.
Such as, if user is forget password, then user can recovery with verified data form.

I tried and test it, not work with my db, as you said. Don't know where is mis-syntex.
I appreciate you reply and explained.

 

[Pat Hartman]

Did you understand what I said about how the combo box works?

I tried and test it, not work with my db, as you said. Don't know where is mis-syntex.
I appreciate you reply and explained.

This doesn't help at all. I have no idea how you changed the code or if you even understood my suggestion. You didn't understand the point I made about using a combo for the UserID. It doesn't matter how the user feels about typing his name. That isn't even close to being relevant. What matters is if management wants you to bother with security at all. If they want you to implement security, then it ought to at least attempt to stop unauthorized users rather than help the user to log in. You can always tell who is logged in by using something like Environ("UserName"). Letting someone create a user ID and log in with it means that you have no security at all.

 

[KitaYama]

My apology delay reply. Thank you very much for your advise . herein some users does not like to type they're user name, the reasons I instead combo.

I given a features to user himself fillup form data details then Administrator will give USERROLES only.
Such as, if user is forget password, then user can recovery with verified data form.

I tried and test it, not work with my db, as you said. Don't know where is mis-syntex.
I appreciate you reply and explained.

Google translate is your friend. I think you better use it to translate the given advices to understand what's been asked to be done.
Apparently you didn't understand what was advised, and you couldn't follow the instructions.
You can also use it to report back the problems you're facing.
Almost everything I post here is a copy/paste from Google Translate.
Here's a link to Google Translate.

Google Translate

Google's service, offered free of charge, instantly translates words, phrases, and web pages between English and over 100 other languages.

translate.google.com

Check the attached database to see if it works for you.

 

[smtazulislam]

Letting someone create a user ID and log in with it means that you have no security at all.

Maybe you dont read this line : "Administrator will give USERROLES only."
Question :
How he is log on database where USERROLES is null. And USERROLES with admin.

 

[Pat Hartman]

How he is log on database where USERROLES is null. And USERROLES with admin.

This question has no context. We don't how your log in form works. You said you made changes to it so that means that the previous file posted has been updated so it is pointless for us to look at it.