Securing a database

This will give you the login and access to the UserID and UserGroup throughout your application:

Make a new module that contains this code:
Public strUserID As String
Public strUserGroup As String

Create table UserList (see below for columns)

Make a login form with control for the UserID and another for the Password. On AfterUpdate property of password:
Dim intX As Integer ' for counting
Dim strPassword As String
Dim dbsCurrent As DATABASE
Dim rst As Recordset
Dim Response As Integer
Dim strSQL As String
Set dbsCurrent = CurrentDb
strUserID = Me.txtUserID
strPassword = Me.txtPassword
Set rst = dbsCurrent.OpenRecordset("SELECT UserId, UserPassword,IsActive,UserGroup FROM UserList", dbOpenSnapshot)
rst.MoveLast
rst.MoveFirst
intUserCount = rst.RecordCount
rst.MoveFirst
With rst
'testing to match userid entered
For intX = 1 To intUserCount
If !UserID = strUserID Then
If !IsActive = False Then
MsgBox "You cannot log in. Inactive account.", vbExclamation
DoCmd.Quit
Else
If !UserPassword = strPassword Then
If IsNull(!UserGroup) Then
strUserGroup = ""
Else
strUserGroup = !UserGroup
End If
Response = MsgBox("Do you want to change your password?", 260, "Change password option") '6=yes, 7=no, 260 sets default to no
If Response = 6 Then
Me.txtNewPassword.Visible = True
Me.txtRetypePassword.Visible = True
lngTries = 0
Me.txtNewPassword.SetFocus
Exit Sub
End If
strSQL = "UPDATE [UserList] SET [LastLogin] = #" & Date & "#, [CurrentlyLoggedIn]=True WHERE UserID = '" & strUserID & "';"
DoCmd.RunSQL strSQL
DoCmd.Close
DoCmd.OpenForm "fmnuMainMenu"
Exit Sub
End If
End If
End If
rst.MoveNext
Next intX
End With

Here's some more code I use at the end of the login code I already posted:

' if password or User ID not correct, give user a total of 4 tries to get it right, then quit application
lngTries = lngTries + 1
If lngTries = 4 Then DoCmd.Quit
Else
Me.txtPassword = ""
Me.txtUserID = ""
MsgBox "Invalid log in. Try again"
Me.txtUserID.SetFocus
Exit Sub
End If

Make a macro called Autoexec that calls the login form to always start the login.

On your main menu form open property, add this code:

if strUserID < "0" then
msgbox "You did't log in!"
docmd.Quit
end if

In the same place on any forms as appropriate you can, for example, only display buttons to certain group(s). For example, a button cmdAdmin on the main menu is only for members of the admin group. Put in this code as part of OnOpen form property:

if strUserGroup = "Admin" then
me.cmdAdmin.visible = True
else
me.cmdAdmin.visible = False
end if

You should set the visible property of those types of controls on forms, etc., to false when you design the form, so it normally is not visible.

Once you've got the login working well, doing the rest really isn't very complicated.Just keep the number of groups down to a small number. You could even include in the table listing the groups some columns that indicate various areas of access that you could just look up for a form, etc., and act appropriately. Using that concept you could have, for example, 10 different areas of concern and the mix of rights could be different but overlapping for the different groups. If you need that complexity, that would be the simplest solution to allow yourself real control while limiting the code necessary in the forms.

Good luck.

Thanks for your help. I am not sure if this is going to work, but as you are the only one to answer and you have gone to so much trouble, you get the points.
PS. Sorry it has taken so long to post this but TR does not let you know when someone addsto their answer.
Thanks again
JV