On 31/05/2010 20:22, Stefan Hoffmann wrote:
hi Bob,

On 31.05.2010 18:21, Bob H wrote:
All I want to do is stop some users from editing the data on the forms,
so maybe I should go for the third option. So how can I do that. I have
been playing around with security and passwords on my own PC, but that
is different from the situation at work.
For example, if I set a password for myself that only requires me to
login, so how can I set a password for another user with write access,
and then one for users with read only access.
In this case you need at least an user table, e.g.

Id, UserName

and an permission table, e.g. FormPermission

idUser, FormName, AllowDelete, AllowEdit, AllowInsert.

Open at the application start a form to query the user, e.g. use the
Windows account name

http://www.mvps.org/access/api/api0008.htm

After closing the this login form, store the user id in a local table in
the front-end, e.g. CurrentUser.

Use a public funtion in a standard module to set the permissions:

Public Function FormApplyPermissions(AForm As Access.Form) As Boolean

On Local Error GoTo LocalError

Dim rs As DAO.Recordset

Dim sql As String

sql = "SELECT FP.* FROM CurrentUser CU " & _
"INNER JOIN FormPermission FP "
"ON FP.idUser = CU.idUser " &
"WHERE FP.FormName ='" & AForm.Name & "';"
Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot)
If Not rs.Bof And Not rs.Eof Then
AForm.AllowDelete = rs.AllowDelete
AForm.AllowEdit = rs.AllowEdit
AForm.AllowInsert = rs.AllowInsert
Else
MsgBox "Unkown form."
AForm.AllowDelete = False
AForm.AllowEdit = False
AForm.AllowInsert = False
End If
rs.Close
Set rs = Nothing

Exit Function

LocalError:
MsgBox "D'oh!" & vbCrLf & Err.Description

End Function

Use this function in all form load events:

Private Sub Form_Load()

FormApplyPermissions Me.Form

End Sub


mfG
-- stefan --

Hi Stefan,
I have created tblUser and tblPermissions with the required fields as
well as creating a module for network login as per the link, but should
this be a form?

Then I copied and pasted the code above into a new module, but when I
come to save it, there is a compile error he

"ON FP.idUser = CU.idUser " &
"WHERE FP.FormName ='" & AForm.Name & "';"

Thanks