How to create user login form (MS Access 2000 - 2003)

Hi,

Thanks for your sample database.

After Logging in to the database, I would like to add a new form that can add a user.

I have 4 textboxes

1. Name
2 Email Address
3. UserID
4. Password

I also have a command button, Add user

The textboxes should all be clear at first. Then after filling up the textboxes and when I hit the Add user button, The profile should be saved in the database and then all the textboxes should be cleared.

Thanks for your time..:)

Good afternoon,
I have used your login form and it is quite useful and easy to adapt to my needs. I have several processes that run and I would like to stamp them with the user_id that initiates them. I have tried to work with the user_id from the main login screen but have not been successful.

I have limited VBA experience and I was wondering if there is an easy way to 'store' the user_id data for use later?

Thank you.

Longship and angel_001

I have combined your messages into the attached updated sample.

Longship
I have added a form called frm_utility to store the user's login id. This form is opened after a valid user has logged in e.g a valid login user id and password. This form is hidden when opened. The module mod_display_menu has been modified as shown in BOLD
Select Case valid_user

Case 0, 1
strmsg = " Access Denied" & _
vbCrLf & " Contact your Administrator if the problem persists. "
MsgBox strmsg, vbInformation, "INVALID USER ID or PASSWORD"


Case 2
' open and hide the utility form frm_utility
' this form will be used to store the user id for as as required e.g recording the user id of an
' user adding records etc.
DoCmd.OpenForm "frm_utility", , , , , acHidden

'store the user id on the utility form frm_utility
Forms!frm_utility!user_id = Forms!frm_main!user_id

When you want to stamp a process with the user's id you would something like this user_id=forms!frm_utility!user_id

angel_001

Log in as TEST1 with a password TEST1 you will see two menu items for adding a new user, the first menu item will display the users using a continuous form, I use this method at work. The second menu is a per your message in that it will open a blank form called frm_add_user_form, the user enters the data then clicks on the Add New User button. Clicking on the Add new user will add the screen data to the users' table tbl_users, after the data is added to the table the screen is cleared ready for another user entry. When you click on Close and Exit the form closes without saving the data entered on the screen.

I was trying to put the code within each of the access levels and it wasn't working. I already had a form to house the user name so it was just a minor name change.

I appreciate all of your help. This has been an invaluable solution and it is helping me further understand VBA (a little).

Thanks again.

hi allan
i have read all the threads concerning user login and downloaded your samples. thanks for the samples. I was wondering if the user login can also apply to the mysql user accounts. I use Access 2003 as a front end and the actual database is made and stored in mysql. The ODBC connections work great my forms and tables and querys work great i just want to set up a user login on the front end sign using the users that were set up in mysql with what ever rights i gave to them there. After i do that i can handle all the other things on the thread here because u have everything so well written. Just need help connecting the user login to the mysql user accounts., your help would be greatly appreciated.. thanks.

ben

Ben

Thank you for compliments.

I do not know how this will work with your mysql user accounts as I have no knowledge of mysql. Basically, when a user logs in their access level is checked and the relevant menu is opened. The only way to find out is to "give it a go".

Regards

Allan

Hi Allan,

Thank you for posting your login utility. I am trying to adapt it to my own program however I am not able to go into design view. How were you able to make it so that the users can not change the view type? This will be useful to me in designing my own programs.

Thank you for you assistance.

What do you mean by " How were you able to make it so that the users can not change the view type? " On startup the Login form is displayed and the user does not see the forms, tables etc.

I have attached a demo version that will not display the Login form on startup. Please contact me if you require more information.

Allan,

Thank you for your response. I meant that I could not put the form in form view to view the code. I can accomplish that with the new one you attached, but I would like to make it so my users can not go in to design view, so I wanted to know how you did that.

I sent an email to your email listed in this thread earlier today. I am trying to design a login and password for my users so that i will know who is logged in, should I need to make changes, I need to know who to tell to close the DB so that I may make my changes.

I am having trouble figuring out how your buttons work and making my own based off the logic.

This part:
'Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9
I did not understand, but after lookin at it for a while I came up with something.

After successfully logging in, a form comes up with the options available to a user with a certain level of access granted to them. I try to see the code behind that form and I am having trouble trying to understand it.
I have modeled my switchboard tables after yours. I have deduced that:
-Each table represents, given the user level of access, the items the user would see on the switchboard.
-ItemNumber represents the button number (ex. HandleButtonClick(4)) which refers to the 4th button on the form?
-ItemText represents what the button does (ex. Add New User)
-Command represents the action taken when the button is pressed
(Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9)
-Argument represents macro or form to be opened if the command is taken
-SwitchboardID represents ?--I am not sure.

I thought I figured it out, but then I signed in to see if each screen would be different based on the user and their access level;everyone had the same switchboard after signing in.

I am trying to understand your model so that I can make my own, please explain the above mentioned items. Thank you

Hi Allan,

I have made some progress since I wrote my inquiry yesterday.

I have found information regarding my questions I asked in my earlier post. I am still having trouble though. I used your demo to make my own version to work with my system. I have a question about the switchboard levels. I understand that each switchboard table represents a diff level of access. I have 3 tables. My question is concerning the tables' first entry:
SwitchboardID: 1, ItemNumber: 0, ItemText: Main Menu, Command: 0
After loggining into my system (trying to access level1), I am prompted to enter the ItemNumber.
When trying to log in as user 3, after entering the username and password, I get a message that says this method is not supported. Can you tell me how to step through the issue to determine where the error is?

I have not seen that prompt to enter the ItemNumber.

Would you please upload a copy and I will have a look at the problem

Yam716

This prompt for ItemNumber is displayed because the field named ItemNumber could not be found in the relevant switchboard table.

If you changed the name of the fields in your switchboard tables then you will need to change the coding behind the switchboard forms or rename the field that was originally named ItemNumber back to the original name ItemNumber or you could also change the coding.

Allan,

I like it a lot, only problem is that when i tried the demo i only got level one menu regardless of who i loged in as, any idea why? it always gave me switchboard_lev1.

boanet

It was my mistake, when I made copies of the switchboard_lev1 for the other switchboards I did not change the record source and some minor coding on the new switchboards.

Attached is an updated version. Please contact me if require assistance etc.

17/11
Sorry, All I forgot to upload the zip file.

Allan, I like your demo file and am trying to incorporate it into something I am working on.

I created another table with some record info, the last two columns being an ModifiedDate and Modifiedby.

I then created a form to enter the records and set a before update event to record the time the record was modified, what I can't get is it to record the modified by, it asks me for the user id. Since I logged in when the db starts I thought I could pass the variable but it doesn't seem to be working. any ideas?

thanks