set currentuser

[Jaye7]

I have the following script which sets the currentuser, however it clears currentuser when I open the database and I don't want this, I want the currentuser to be there and it only changes when I want it to change.

I.e. when the database opens if no value is found in my table then the name stays the same, if a value is found in my table then it will change to that name.

The following script is to check if a value is in the table, I have tried adding this to the script that sets currentuser but the currentuser is stilled cleared upon opening the database due to the function..

Dim TableName As String
TableName = Nz(DLookup("CurrentUserID", "UserInfo"))
If TableName <> "" Then gblusername = TableName

this is at top of module

public gblusername as string

this is the function that I use

function currentuser as string
currentuser = gblusername
end function

Set currentuser manually if I want to

gblusername = "Jim"

 

[Fuse3k]

What type of module is being used for the line?

public gblusername as string

Is it the Form's Module, a stand-alone module (i.e. "Module 1"), or a class module?



If you're looking to store a variable globally as long as Accessis running or until you change it, try using TempVars:

Tempvars.Add "gblusername", "Jim"

You can call it from pretty much anywhere(form, reports, tables, queries, vba)

Call it using something like this:

Dim strUserName as string
strUsername = Tempvars("gblusername")

hth,
Fuse

 

[vbaInet]

You haven't really explained where the CurrentUser() function is getting its value from.

In the Control Source:

=Nz(DLookup("CurrentUserID", "UserInfo"), [COLOR="Blue"]CurrentUserFunction[/COLOR]())

CurrentUser is a reserved keyword in Access so you should consider renaming it to something else.

 

[Jaye7]

the function is in a stand alone module.

I have tried changing the following but then get an error when trying to set currentuser along the lines of left function variable error.

function Setcurrentuser as string
  Setcurrentuser = gblusername
  [COLOR=Red][B]Currentuser = Setcurrentuser[/B][/COLOR]
   end function

the following gets the value from the field named CurrentUserID of the table named UserInfo, there is only ever one row in the table so I don't have to declare something like CurrentUserFunction

TableName = Nz(DLookup("CurrentUserID", "UserInfo"))

I will check out the Tempvars script, as maybe that will work

 

[vbaInet]

the following gets the value from the field named CurrentUserID of the table named UserInfo, there is only ever one row in the table so I don't have to declare something like CurrentUserFunction

I was telling you to rename your function from CurrentUser to something else. Rename whichever function, sub, control etc that's called CurrentUser because it's a reserved keyword for Access. The code I wrote should go into the Control Source like stated and it will alternate between the field value in your table or grab the value from the function if there's no record in the table.

Whether you use a TempVar or a global variable it won't make any difference. They are interchangeable for this sort of job.

How and where do you set the global variable?

 

[Jaye7]

The global name is set when the first form opens, it checks for the value in the table and if no value is there then it leaves currentuser as the user name that they logged on with, it's because sometimes the user name is set by getting to the database from another database and I want to store the user name in a table to run certain controls. But if I am in sometimes session then I might want to temporarily set the current user to me just to see certain hidden controls and then set it back again.

 

[vbaInet]

Still not fully answering the questions. How do you set the global variable? Let me see the code or tell me the routine that sets the value of this global variable. Where and how do you set the gblusername value?

 

[Jaye7]

The code to set the currentuser is listed in my first post, it is in the form open event, this is where I was having the problem, it was setting the currentuser to null as nothing was in the table.

Dim TableName As String 
TableName = Nz(DLookup("CurrentUserID", "UserInfo"))
If TableName <> "" Then [COLOR=Red][B]gblusername = TableName[/B][/COLOR]

 

[vbaInet]

Ok, thanks for clearing that up. But what is not clear is this -->

Set currentuser manually if I want to

gblusername = "Jim"

How do you intend to set user name manually? You want to be able to type it directly into the textbox?

If that's the case then here's how it should be setup.

In a standard module:

public gblusername as string

public function GetCurrentUser as string
    GetCurrentUser = gblusername
end function

public function SetCurrentUser as string
    gblusername = Nz(DLookup("CurrentUserID", "UserInfo"), vbNullString)
end function

In the Open event of your form:

Call SetCurrentUser
Me.txtCurrentUser = GetCurrentUser()

So, if the above returns nothing, you can type it into the textbox directly.

 

[Jaye7]

I set it manually through buttons or form update events etc... I will check out the code when back at work in a couple of days time.

Thanks for your help, much appreciated.

 

[Jaye7]

so I tried the script that you provided, it sets the gblusername, but how do I then set the currentuser to gblusername.

I tried the following but get an error

Public Function SetCurrentUser() As String
    gblusername = Nz(DLookup("CurrentUserID", "UserInfo"), vbNullString)
    currentuser = gblusername
    'MsgBox gblusername
End Function

Error: compile error: function call on left hand side of assignment must return variant or object.

I added in dim currentuser as string which stops the error, but the currentuser is not set.

 

[vbaInet]

I set it manually through buttons or form update events etc...

This is the logic I'm not understanding. So you open a form and enter a username into the textbox if the code returns nothing right? Is this a single user db? Because if it isn't then it's not making sense.

Let's take a few steps back, forget about code and iron out this logic first. Code is easy to implement.

 

[Jaye7]

It is a single user database.

Skip the open form event for now, as a button is more important to set when I want too.

I basically want to set the current user from a command button. So at the moment the user is in their database with their currentuser name set as say Bob, then I click the button and it sets the currentuser as my name so that I can see hidden controls etc... while talking to them about functions in the database.

Then I click a button and reset the currentuser back to their name, so I originally store their name in a table when I click the first button, so that when I hit the reset button it knows what the original currentuser was.

 

[vbaInet]

You're still not explaining this fully, so I'll ask the question differently.

1. How do you capture your user name? You have a form with a textbox where you enter the temporary replacement value? Or your name is already saved in a variable somewhere?

2. If the user's name is saved in a table, why would the DLookup() ever return Null?

Please answer the questions specifically.

 

[Jaye7]

When I use the set method from the first button it will store the currentuser name in the table named UserInfo, so I use an update query script to add the currentuser name into the field named CurrentUserID, so the original user name/currentuser name is always stored from that point on, it isn't stored when the database is opened it is only stored when I want to change it from the first button.

I can get the currentuser by simply using

Dim SetUserName as string
SetUserName = currentuser

then update the table.

The whole issue is finding a script that doesn't overwrite the existing currentuser until I want to set it from the first button, the table will have a null value until I hit the first button then it will store the currentuser, then I will set my own name as the currentuser. Then when I use the second button it will set the currentuser to the original user name that was set from the first button code.

 

[vbaInet]

Jaye7, I've asked specific questions a couple of times but you keep describing the process. If you can answer the questions in my last post we can work towards a solution. If you don't understand my questions, then please ask for clarity.

 

[jdraw]

Can you describe in plain English -- no Access functions or jargon -- what you are trying to do? My best guess is that you want some "things" hidden for some users and exposed/visible to other users.

You have shown us How you have done something that isn't what you want. We need to know what before we can advise on how.

If you have a list of "registered/official users" and an indicator of what they should or should not see, then you may be able to compare the user with the registered user in the "registered/official users" list and take appropriate action.

But it isn't clear to me what you want to accomplish.

You can find info on the internet by searching
Role Based Access Control to see if this is what you are trying to do.

 

[Jaye7]

OK, here goes.

A user logs on to a database using their user name and password.
Access stores their user name and password automatically as the currentuser (no script required, as it is an automatic Access process when anyone logs on).

There is one command button (Cb1) on a form that I will use to change the current user to my name, which when it does this it stores the previous logged on currentuser in a table, as we may go to numerous forms before resetting the current user from my name back to theirs.
Forget about why I want to do this (i.e invisible controls etc...) at the end of the day I just want to be able to it for whatever I feel like using it for.

Then when I get back to that same form it has a command button named Cb2, when I click on Cb2, it resets the currentuser from my name back to theirs, using the name that was previously stored in the table.

 

[jdraw]

Interesting. An often discussed topic.

It is often helpful if readers know What you are trying to do and Why to help focus responses/alternatives. However, you have said

Forget about why I want to do this (i.e invisible controls etc...) at the end of the day I just want to be able to it for whatever I feel like using it for.

So, here is a more generic answer, I suggest you research Role Based Access Control

 

[vbaInet]

There is one command button (Cb1) on a form that I will use to change the current user to my name, which when it does this it stores the previous logged on currentuser in a table, as we may go to numerous forms before resetting the current user from my name back to theirs.

Then when I get back to that same form it has a command button named Cb2, when I click on Cb2, it resets the currentuser from my name back to theirs, using the name that was previously stored in the table.

You keep mentioning currentuser without explicitly explaining what currentuser denotes. So if you're talking about Application.CurrentUser or Environ("USERNAME") then those are read-only methods. You have to log out and log in as yourself if you want that to return anything different.