Access login interface

[Fred Ho]

I have an access database, and want to make an login interface for (guest/staff/administarator).
The guest should just able to use the form with buttons search/print only.
The staff able to use the other buttons for editing the records. The admin should able to modify the prog. vba and modify password as well.
Pls teach me in steps how to make such login interface. (such as how to disable the by-pass function, how to make the code.. any sample? how to make the database able to let the staff and admin able to paste the records from Excel to the access table?)
Thanks a lot for your kindly help!

[convexcube]

I have an access database, and want to make an login interface for (guest/staff/administarator).
The guest should just able to use the form with buttons search/print only.
The staff able to use the other buttons for editing the records. The admin should able to modify the prog. vba and modify password as well.
Pls teach me in steps how to make such login interface. (such as how to disable the by-pass function, how to make the code.. any sample? how to make the database able to let the staff and admin able to paste the records from Excel to the access table?)
Thanks a lot for your kindly help!

Hi Fred,

I''ll give you a quick rundown of the way I have done this:

1. Place an unbound text box control on the form called - "txtUserLoggedIn", set the labels caption to "User logged in:", and buttons called - "cmdLogOut" & "cmdChangeUser"
2. Create a new unbound form called "frmLogIn" with the controls: text boxes - "txtUserName", "txtPassword", buttons - "cmdLogIn", "cmdCancel". Set control box to "no", close box to "no", modal to "yes", pop up to "yes" & border style to "Dialog". On the cmdChangeUser click event have this code:

   DoCmd.OpenForm "frmLogIn"

3. On the main form open event have this code:

   Call cmdLogOut

4. In your user table have fields for user type, password. I will assume these are called "fldUserType" & "fldPassword", that user table is called "tblUsers" and that your staff name field is called "fldUserName". Change my references to suit your naming scheme.
5. fldUserType should use a lookup list with the values: "Staff" and "Admin" and fldPassword should have an input mask set to password.
6. On the cmdLogout Click event have this code:

   txtUserLoggedIn = "Guest"

   After this you will need to fill all the controls you don't wish guests to have access to with:

   controlname.Enabled = False

   where "controlname" is the name of your controls.
7. On the new form "frmLogIn" the cmdCancel Click event should have this code: DoCmd.Close acForm "frmLogIn".
8. Now for the nitty gritty: On the cmdLogIn Click event have the following code (I will be using some assumed names here so be sure to change them to match your naming scheme):

   If IsNull(txtUserName) Then
       msgbox "Enter a username"
       Exit Sub
       Else
       If IsNull(txtPassword) Then
           msgbox "Enter a password"
           Exit Sub
           Else
           If IsNull(Dlookup("UserName", "tblUsers", "UserName=" & chr(34) & txtUserName & chr(34))) Then
               msgbox "Invalid user name"
               Exit Sub
               Else
               If Not Dlookup("Password", "tblUsers, "UserName=" & chr(34) & txtUserName & chr(34)) = txtPassword Then
                   msgbox "Invalid password for " & txtUserName
                   Exit Sub
                   Form_[your current form name].txtUserLoggedIn = txtUserName
                   If Dlookup("Type", "tblUsers, "UserName=" & chr(34) & txtUserName & chr(34)) = "Staff" Then
                       [enter code to enable controls that staff have access to - no need to code for controls that guests have access to, and disable controls that administrators have access to but staff do not]
                       Else 
                       If Dlookup("Type", "tblUsers, "UserName=" & chr(34) & txtUserName & chr(34)) = "Admin" Then
                           [enter code to enable all controls that administrators have access to - no need to code for controls that guests have access to]
                           Else
                           msg box "You have guest access"
                           Form_[your current form name].txtUserLoggedIn = "Guest"
                       End If
                       DoCmd.Close acForm "frmLogIn"
                   End If
               End If
           End If
       End If
   End If

I think I've got everything in there that needs to be. Let me know how it goes.

Hope you & your family have a great Christmas and holiday season.

Kind regards,
Ken.

[Jim Doherty]

I have an access database, and want to make an login interface for (guest/staff/administarator).
The guest should just able to use the form with buttons search/print only.
The staff able to use the other buttons for editing the records. The admin should able to modify the prog. vba and modify password as well.
Pls teach me in steps how to make such login interface. (such as how to disable the by-pass function, how to make the code.. any sample? how to make the database able to let the staff and admin able to paste the records from Excel to the access table?)
Thanks a lot for your kindly help!

To control permissions users and groups of users within the framework of the integral Access security model you should look at using a workgroup administrator file these files typically have an .MDW file extension the default file under which 'Access' operates is called system.mdw and is unsecured by default with the default user being called 'Admin'.

There are several steps to securing an application using the model, however once mastered user accounts, permission rights including insertions and edits to tables 'directly' are controlled by the model. You are presented with logon dialog automatically once the model is invoked and navigation can be controlled by reference to the function CurrentUser() that user of which can be assigned to different groups and can inherit permissions of these groups and so on.

Using this method will 'for the most part' will prevent other users of retail installations of Access from opening your database unless they are part of the workgroup file which of course you as administrator control.

Be wary though that these days it is not 'totally' secure per se one can purchase third party applications that can read the mdw file and provide the user name and password which somewhat defeats the purpose of it all.

Suffice it to say this it was MS's answer to access security at the time written with security in mind. Pretty much like anything else, its only a matter of time before commercial interests to undermine it take over. I haven't yet seen the 'back hand shot down the line' from MS to send the tennis ball back over the net to win the match.... so to speak

Take look at this MS link to get you started

http://support.microsoft.com/kb/289885

Jim :)

[Fred Ho]

Thanks for the previous reply!
But I still have following question:
1- If I have a login form already, how to set it to modal, pop up and acDialog??
2 - after setting to modal the bypass problem can be solved?
3- for checking the user (staff and admin) i think i will call the form for them which is able to edit but how to let them to add the records from excel using cut and paste the records in batch to access table of the record again?
Can I add any code for such purpose?
Thanks a lot!

[convexcube]

Thanks for the previous reply!
But I still have following question:
1- If I have a login form already, how to set it to modal, pop up and acDialog??
2 - after setting to modal the bypass problem can be solved?
3- for checking the user (staff and admin) i think i will call the form for them which is able to edit but how to let them to add the records from excel using cut and paste the records in batch to access table of the record again?
Can I add any code for such purpose?
Thanks a lot!

Hi Fred,

1. Open the form in design view and activate the properties window, you should find those parameters in there, just make sure you choose form from combobox at the top.
2. I'm not sure what you mean by 'bypass'. If you can explain it in more detail, I might be able to help.
3. There are several ways to integrate excel. I haven't yet had to do this so I'm not sure what steps to take but there are several good articles on this site and others on the internet. To copy & paste as you suggest, you would probably need to do that directly to the table though rather than through a form.

Hope this helps somewhat.

Kind Regards,
Ken.

[Fred Ho]

I will make a login form for different users (guest/staff/admin) such that the guest not able to edit or change the existing record.
So the database may need to be protected not to let them able to see the record table ( not let the guest to make modification ).
As if the guest know the by-pass (i.e. press shift and click the file to open the mdb file) he may able to see the table in design view window if not protect or disable the by-pass function.
However, as we (the staff and admin) are building up the access using the excel records and hope to copy and paste the table form excel to access directly as it seens to be most convenient and there are lots of the records.
So how to make such login or program code? As I am really start from zero to make the database by myself and already made one now. So thanks for all your experts who are really more familiar to access than me.
my questions are:
- if I have the intreface form (let the user to select ..guest/staff/admin) and then password and also the the database forms (search/edit/add..etc.). How to make the login form to match what I form?
- is it making the form porperty to modal, popup and call the form using acDialog can solve the bypass function (prevent the guest to see the table?) but how to make modification if I he is staff and admin as they need to paste the record to the table in access?
perhaps I may put the password in the vba first as the code is protected and I think more safe.
- if yes how to set such porperty?
- I will use the code provided in the previous reply ( thanks for the help) but there are some parts missing. I think I will call the form for guest if they are guest, so that they are able to search and open the other form if they are staff or admin. But the problem is how to let them able to past the excel record to access? Do I need to activiate the design view window? How? I really need help for this !
- is there any better method than making the the forms? or just call the same form but with the add/ edit record buttons dim at the form? or other suggestion?
So may for my questions but hope it will be clear. Thanks!

[convexcube]

Hi Fred,

Now that I understand what you mean by the bypass function, I have a few points.

1. All the modal property of the form does is prevent a user from using any other window until the form is closed. acDialog is a window type that ensures the form is in a self contained window. Neither of these properties will prevent users from accessing the whole database if the shift key is held down on startup.
2. By doing a search on this site, I have found code that will prevent the user from using the shift on startup method. Assuming that Allow Full Menus and show database window (or navigation pane in 2007) is disabled in the database properties, this code alone means that the user would not have access to change the database through its tables etc. To enable this functionality you must set up a control (with a password if desired) that will disable the code for the next startup. So in effect, you would have to start the database, set the control then restart the database with the shift key held in order to edit tables etc.Link 1 Link 2 Link 3
3. Instead of pasting the records from excel into the tables in access, have you considered importing or linking them through the functionality that access provides? Or what about using access to enter these records directly in the first place. I'm not sure what your system entails, but there are many solutions that don't involve cutting and pasting, which might negate the need to let your staff get in with holding the shift key down and leave that entirely for you - the database designer, which would help with the security and integrity of the database. Do a search on this site and the internet to see how this can be done. Activating the design view window isn't going to help you here.
4. As for how you structure the forms, that is entirely up to you. Making it intuitive and easy to use though is a most important point. The code I provided will give you a good starting point but obviously your situation like everyones is unique and requires trial and error to make it work. The best thing about that is that your learn so much from it. So give it a go, find out what's not working and nut out a solution one issue at a time.

Hope this helps!

Happy coding,
Ken.