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 --