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

Access login interface

P: 18
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!
Dec 23 '07 #1
Share this Question
Share on Google+
6 Replies


convexcube
P: 47
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:
    Expand|Select|Wrap|Line Numbers
    1. DoCmd.OpenForm "frmLogIn"
  3. On the main form open event have this code:
    Expand|Select|Wrap|Line Numbers
    1. 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:
    Expand|Select|Wrap|Line Numbers
    1. txtUserLoggedIn = "Guest"
    After this you will need to fill all the controls you don't wish guests to have access to with:
    Expand|Select|Wrap|Line Numbers
    1. 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):
    Expand|Select|Wrap|Line Numbers
    1. If IsNull(txtUserName) Then
    2.     msgbox "Enter a username"
    3.     Exit Sub
    4.     Else
    5.     If IsNull(txtPassword) Then
    6.         msgbox "Enter a password"
    7.         Exit Sub
    8.         Else
    9.         If IsNull(Dlookup("UserName", "tblUsers", "UserName=" & chr(34) & txtUserName & chr(34))) Then
    10.             msgbox "Invalid user name"
    11.             Exit Sub
    12.             Else
    13.             If Not Dlookup("Password", "tblUsers, "UserName=" & chr(34) & txtUserName & chr(34)) = txtPassword Then
    14.                 msgbox "Invalid password for " & txtUserName
    15.                 Exit Sub
    16.                 Form_[your current form name].txtUserLoggedIn = txtUserName
    17.                 If Dlookup("Type", "tblUsers, "UserName=" & chr(34) & txtUserName & chr(34)) = "Staff" Then
    18.                     [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]
    19.                     Else 
    20.                     If Dlookup("Type", "tblUsers, "UserName=" & chr(34) & txtUserName & chr(34)) = "Admin" Then
    21.                         [enter code to enable all controls that administrators have access to - no need to code for controls that guests have access to]
    22.                         Else
    23.                         msg box "You have guest access"
    24.                         Form_[your current form name].txtUserLoggedIn = "Guest"
    25.                     End If
    26.                     DoCmd.Close acForm "frmLogIn"
    27.                 End If
    28.             End If
    29.         End If
    30.     End If
    31. 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.
Dec 23 '07 #2

Jim Doherty
Expert 100+
P: 897
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 :)
Dec 24 '07 #3

P: 18
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!
Dec 27 '07 #4

convexcube
P: 47
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.
Dec 27 '07 #5

P: 18
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!
Dec 29 '07 #6

convexcube
P: 47
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.
Dec 29 '07 #7

Post your reply

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