By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,435 Members | 2,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,435 IT Pros & Developers. It's quick & easy.

Autopopulate a field based on logged in person.

P: 111
I have custom login interface. When the user logs in I want to show something like "Welcome John Doe" on my switchboard form? How to do it? Hint please!!
Also, when the user selects "Open data entry form", the field called ModUserID should be populated with the person who is logged in right now.
Open Data Entry form : frmOpendataentry is bound form. the table it is bound to is tblRadioDataEntry.


PS: I tried to define "Default value" property on both table and/or form. It does not work. When I open the form it sayes "#Name" in ModUserID field.
Dec 1 '09 #1
Share this Question
Share on Google+
11 Replies

Expert 100+
P: 1,287
You can use the code here [] to get the login name of the user. You will likely want to have a table of login names, actual names, and other info for users.
Dec 1 '09 #2

Expert 100+
P: 234
You need to create a table that has each user's real name, username, etc. The user will log into the database using their username, then you will use the username to pull the real name from your users table.

If you are using Access 2003 or earlier with User-Level Security set up, you can get their username via the CurrentUser() function. If you are not using User-Level Security (which is really the better option) and you don't have any other secure login mechanism set up (which you really need), you can get the username from Windows using the ENVIRON() function. Check this thread for more on security and the ENVIRON() function
Dec 1 '09 #3

Expert 100+
P: 234
There may also be a problem here with terminology.

By "custom login interface" do you mean an actual login form where the user enters a username and password, or do you mean a customized switchboard?

You say that when the user opens a form, a field called ModUserID should be populated with their name. Do you mean a control on the form or an actual table field?

And finally, what are you trying to set the default value of your control to?

Okay, I think I'm done with questions for now. This is what I'd do if I were you.

In the database is the following table:

Expand|Select|Wrap|Line Numbers
  1. tblUsers
  3. Field           Type
  4. UserID          AutoNum  PK
  5. strLastName     Text
  6. strFirstName    Text
  7. strMI           Text
  8. strUserName     Text    (no duplicates)
  9. strPassword     Text    (encrypted)
  10. strPermissions  Text    (access permissions)    
In a module, declare a public variable
Expand|Select|Wrap|Line Numbers
  1. Public plngCurrentUserID as Long
When the user logs in to the custom login form using the username and password from tblUsers, set plngCurrentUserID to the UserID corresponding to strUserName. Now, whenever you need something regarding that user, such as their permissions or name, you reference plngCurrentUserID. For the field you want to populate, you'd use a couple of DLookup functions, like so, in the ControlSource property.

Expand|Select|Wrap|Line Numbers
  1. ="Welcome, " & 
  2. DLookup("strFirstName","tblUsers","UserID = " & plngCurrentUserID)
  3. & " " & DLookup("strLastName","tblUsers","UserID = " & plngCurrentUserID)
  4. & "!"
This is the method I use. If anyone else knows of a better way, please share!

P.S. For encrypting the password using an MD5 hash, I use Robert Hubley's MD5 class module. I believe the version I am using is available here
Dec 2 '09 #4

P: 111
Thank you guys for the reply!! The mistake I was doing was, I was closing the login form using
Expand|Select|Wrap|Line Numbers
  1.  DoCmd.Close acForm, "frmLogin", acSaveNo
.That is why the value in the txtUserName was not avaialble for rest of the forms. What I did was I replaced that line with this one
Expand|Select|Wrap|Line Numbers
  1. Me.Visible = False
So now, it does catches the value of txtUserName for rest of the open forms.
ANyways, I never thhought this far(like you guys have answered!!) It is nice thought to get an insight.!!
Thanks again!!
Dec 2 '09 #5

P: 111
I guess I solved my questions. I have all the fields you have mentioned in the table except for "strPermissions". Please let me know how I can use this field to set the user level permissions? For ex. Only Supervisor will have access to add more users and not the regular users.
By cutom login interface, I mean no switchboard. I created a form called frmLogin which has data entry fields called UserName and Password and commands like Forgot password, Can't sign in. etc
(the rest of the questions are taken care of, thanks to you guys)
Dec 2 '09 #6

Expert 100+
P: 234

strPermissions was oulined in the thread about passwords and security that I referenced in post #3. The field contains several two-letter abbreviations for different permissions. When a form loads, it checks to see if the user has permission to use different controls by using the InStr() function to see if the controls' two-letter permissions are in the permissions string.
Dec 2 '09 #7

P: 111
Can you give me some example? Where to start, I suppose?? I mean I am getting something like this in my mind.
Expand|Select|Wrap|Line Numbers
  1.          UserId    strFirstName   strLastName       strPermission
  2.             1           John             Doe                Supervisor
  3.             2           Jill                Smith             Manager
  4.             3           Mike             McDonald       User
Can we control access to each and every form via this?
For example, The users who have "Supervisor" permissions, only they can edit data of users under them?
Thanks for help.
Dec 2 '09 #8

Expert 100+
P: 234
The problem with using titles like "Supervisor", "User", "Admin", etc. for permissions is that you run into issues such as a Manager needing access to a resource that normally belongs only to Supervisors. You want to give that Manager access to that area, but you don't want to grant him Supervisor access, nor do you want all Managers to have access to that area. Uh oh, dilemma time. That's where the permissions the way I was talking about come into play.

If you set a different permission code for each asset you want protected, you can assign the code to any user who needs it.

In order to secure a resource and allow users access to it, you would need to set up something in code, like so.

Assume a main switchboard called frmSwitchboard. On the switchboard you have a button called cmdOpenAdmin that opens the Database Administration menu form. Obviously, you don't want everyone getting into that form, since they could wreak some major havoc in there. So, in the OnOpen event of your form, you place code that checks the user's permissions and then sets cmdOpenAdmin.Visible to True or False based on whether they have permission to view it or not. The same would also be done for any control on the form that you don't want all users to have access to.

Does that outline make enough sense that you can run with it?
Dec 2 '09 #9

P: 111
Your explanation does makes sense and I think I will be able to figure out something based on that. Thanks for taking time to explain.
Really, i was not even thinking of having some field like 'strPermission'. But I think this way it will be easier to define groups.
Thanks again!!
Dec 2 '09 #10

P: 3
hi topher23

I have a question for you....i have an access database with several table, one of those being a user table with Username, First and last Name fields. I would like to create a field on another form and have it auto populate the full name of the user based on their login in. Would also like to "lock" this field and prevent the current user or any other user from editing it...thanks
Jan 31 '19 #11

Expert Mod 2.5K+
P: 3,284

Welcome to Bytes!

You may want to take a look at this thread on User Permissions and Customized Menus. Once you understnad that Access recognizes the particular user, you can use whatever you want in the user table at will. You can also expand the User table to include whatever data you desire (such as First and Last Names).

Hope this hepps!
Feb 1 '19 #12

Post your reply

Sign in to post your reply or Sign up for a free account.