Create Login Account

[chan0210]

Hi
I wanted to create a login function at the start of my form. basically, my idea is to have an administrator and users. Administrator is able to view all the information while the user is able to view only the information that they added in the database. Can this be done?

 

[namliam]

Yes that can be done, not easy but can be done.

 

[chan0210]

how can it be done?

 

[namliam]

You can use the access security system and you need to secure all the queries in your database to limit the amount of data returned to the user logged in.

 

[agehoops]

if you wanted to use a custom login script (which i always use) then you could try something like this (haven't tried it but technically should work)

On the login script, set a global variable to the username that has logged in
In the table that the main form is based on (or any record for that matter) create a field which is the username

In the forms, set the value of the username field to the global variable you stored on login

When the user logs in and opens a form, tell it to filter the records by the username field for the username variable. That way when a user creates a record, their login name is stored in the same table and the records are filtered for that name. If you want an admin to see everything, simply run a code which checks to see if it is an admin, and if it is an admin, don't apply the filter, if it isn't an admin then do apply the filter.

Just an idea

 

[chan0210]

thanks. i will look into that.

 

[chan0210]

Hi..

About your idea, how can i implement it technically? as in the steps involved in the process?

 

[agehoops]

ok, firstly you'll need to create a table with users, holding their ID, username, password, and a tick box for enabled (optional)

Now, create a form (login form) which constsits of a drop down box, and text box, and a button. With the drop down box, make it's row source the ID and username fields of the users table. ok, now the main code comes in.

With the button, what will need to happen behind it is this:

First, do a DLookup on the username selected from the list to find their password.
Check to see if the password entered matches the password found for that user.
If it doesn't, give an error message stating so.
If it does, then open the specified form.

Ok this is just for the login script

Create a global variable which can be called anything you like, but something you'll remember, such as "UserLoggedIn"

Now, on the button of the login form, add the code that say when the username and password are correct, set the new global variable to be equal to the user that has just logged in (You've already got this information with the DLookup in the login script)

Ok, now, for every table that will be storing data (or at least, the tables which have forms based on them) create a new field, which is for the user who added that data.

Ok, now, on each of these forms, add the new field and set it to invisible so the user can't see it. When a user creates a new record, you want the code to set the value of this field the the value of the "UserLoggedIn" variable (hopefully this should be an ID number)

What should now happen is for every person who creates a new record, this field should put that users ID in the field that they can't see, so it is recording who created the record.

The next step, is to have the OnLoad even of the form, filter the data on the ID number stored in the "UserLoggedIn" variable. You can also have a failsafe, that checks to see if the userloggedin is the Admin, and if so, don't filter, but if not, then filter everything based on the ID number of who created it, filtering that field.


Hopefully this makes sense?? It is quite a complex way but can be very customizable if done properly.

Let me know if you need any more help with it.

Adrian

 

[MicroE]

Example:
http://www.databasedev.co.uk/login.html

 

[agehoops]

The exact script i based my logon script on infact

 

[chan0210]

Hi Adrain,

the steps that you are given are quite detail but i have problems with the codings. I do not know how to convert your instruction to the respective codings. Do you have a sample program or something which i can reference?

 

[chan0210]

Hi MircoE,

i have seen this sample but its just a simple login without any security level. So i did not consider this.

 

[DRathbone]

My recent enquiry sounds like it will help you out
http://www.access-programmers.co.uk/forums/showthread.php?t=123694

 

[MicroE]

The first step is to create the login form. Once you have the username you can store it the table and filter the records based on the username.

http://www.databasedev.co.uk/filter_current_user.html

 

[agehoops]

Yea i'm sure i can get you an example. What i'll do is strip out the one i use on my current system i'm developing to you can see the sort of thing i am using, but it is only the login system and the restriction system, but i'll get that uploaded in the next few hours when i've finished everything i've got goin. May be sooner

 

[agehoops]

Ok then here ya go. This is basically the login script with the ability to restrict different user types access for the situation i am currently designing for (you can adapt to however you need easily though) it is quite complicated (LOADS of DLookups) and half of which are not needed for this i just didn't delete them all. There are 4 user accounts on this, they are:

admin - test
carer - test2
other - test3
RGN - test4

So use those to login. the buttons on the main switchboard have been disabled, and are there just to show the effect of restricting access. Log in as the admin and you'll be able to access the admin form through the switchboard, where you can choose who can see what (half of the check boxes won't change anything as those parts are deleted but some still work, sure you can figure out which ones)

Hope this is what you're after. If you slightly adapt it, and use the code MicroE posted for filtering by the username

Hope this helps

View attachment Login Script DB.zip

Thanks
Aidy

 

[chan0210]

i will look into it. thanks guys for the infor.

 

[jserrone]

Hello,

I have the attached database with a logon screen. I would like to be able to open a separate form for anyone logging in as an Admin. Bellow is the following VB code used to open the WelcomeForm once they authenticate the logon. Is there a possibility to add that anyone accessing with Admin only would have access to the form XXXX

Thanks

'Check value of password in tblEmployees to see if this matches value chosen in combo box
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then

lngEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.OpenForm "WelcomeForm"
DoCmd.Close acForm, "frmLogon", acSaveNo

 

[agehoops]

Yea shouldn't be too much of a problem. Do you have a field in the table which states what type of user they are? Or which indicates whether the user is an admin? If so then you really just need an IF statement.

Just put in a clause to check if the user that has just logged in is and admin. If they are open form X, if not, open form Y. You just need to have a way of differentiating between admins and others