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.

 

[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.

 

[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...

 

[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.

 

[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.