Creating Security on Forms for Access 2007

Can anyone give me some direction in applying security to my forms in an Access 2007 database?
It is still in the development stages, but when complete I will be splitting the database so that multiple users can access it.
I have an AdminTbl with user names and another table with security access levels. Each user is listed in the table with an access level, e.g. 0 = ReadOnly; 1 = Read/Write, etc.
I would like to set security on forms based on the users Windows login name and create a way to add the security to new forms.

My plan is to either create a dashboard that will launch on startup, according to user name.
Or on startup only open the appropriate forms, based on user name, and hide all other objects.

This is basically the first project I have done in Access 2007 and the first time I have incorporated VB in an Access DB.

Point well taken on the groups I think that was where my mind was going.

In Access 2010 there are no security options that I am aware of. The only way you can limit access to forms etc. is programmatically. If there is a way to set security I'd be very interested in knowing how to do that.

The users are pretty smart. I don't think they wouldn't by-pass security with malicious intent. But may do so to figure out a way to get the job done if they run into issues. Before you say "the database should be designed in a way that allows them to do their job without having to look for a "workaround". I agree, but sometimes weird things happen and some workers will not want to bother me and will look for a way to resolve an issue on their own.

I see what you mean about 2007 security. I am still on 2003 as that's where all my clients are. I have used Access security in the past and then had to program it to give some users one lot of functionality and others another form of functionality. However it is fraught with issues and if not set up exactly right easily broken. I have broken into many a "secure database" using the stock std security file and the shift key. So not having it available is not a bad thing.

For starters make sure they get a compiled version of the database eg accde.

Disable the shift key for users and hide the database window on startup. You will have to code this. (Make sure you keep a copy without the shift key being disabled.)
Or you could add additional functionality so that if you are the user the shift key is enabled and if not the shift key is disabled.

You could provide various front ends for different groups of users, but this will quickly lead you into the nightmare of multiple front ends to support so try to avoid going there.

You are going to have to code this.
So put the users in tables and assign then to groups
Add a table to the database assigning buttons of the dashboard to groups.
When the user opens the database you will have code in place to check their level of security based on their windows user name and display the appropriate buttons for that user.

Try to avoid making the user log into the database. They have already logged onto windows and double logging annoys them especially when the two passwords are different as they tend to become over time.

You may also want to allow another trusted person the ability to add users to the database other than yourself as if you go away on holiday a new user will be given access via someone else's windows login. It happens all the time.

Hope you have split the database into front end and backend or better still used SQLServer for the backend.

In some of the applications I have created I have used code such as the sample below. The "funGetFormAccess" reads a table that contains the user's windows logon, the name of the form "frmAccessControl" and a Yes/No column that indicates if the user can edit the form or not. The function returns the value from the Yes/No column. If the value is True, then I set a number of properties that control whether or not the users can edit, delete or add records.

If you want to display or hide button, set the Visible property for the button.