Microsoft Access VBA Macro Programming

X = GetUserName(strUser, 256 )

strUser = RTrim(strUser)

strUser = Left(strUser, Len(strUser) - 1 )

Set RecSet = CurrentDb.OpenRecordset _

("select * tblUserRights where UserName=’" & strUser & "’")

If RecSet.RecordCount Then

ReturnUserLevel = RecSet!UserLevel

Else

ReturnUserLevel = "unknown"

End If

Set RecSet = Nothing

End Function

This public function uses the API callGetUserNameto return the Windows login ID of

the current user. It then opens aRecordsetobject based on the table tblUserRights, where the

UserName field is equal to the login ID and returns the user level. If no record is found, it

returns “unknown”.

You can now set up rules on your forms as to how they will work in relation to the user

level.

For example, if the form will be one that only administrators can use, such as for setting

up new users, then you would put the following code on the form activate event:

If ReturnUserLevel <> "administrator" Then

MsgBox "You are not authorized to view this screen", vbCritical

DoCmd.Close

End If

Similarly, you can use the following code on a form if you want “basic” users to have

read-only access, and all other user types to be able to change records:

If ReturnUserLevel = "unknown" then DoCmd.Close

If ReturnUserLevel = "basic" Then

Me.AllowAdditions = False

Me.AllowEdits = False

Me.AllowDeletions = False

Else

Me.AllowAdditions = True

Me.AllowEdits = True

Me.AllowDeletions = True

End If

Notice that there is an option for an unknown level that closes the form, although in theory

an unknown level user should not get this far.

356 Microsoft Access 2010 VBA Macro Programming