Permissions Question

[paulbrog]

I am running Access 03, and have two tables, I would like three columns in one table to be edited by some employees, and full permission of editing fields by others. Can this be accomplished through the security settings? Ideally two separate passwords would be used, one for admin, and one for regular employees, similar to a Windows login, then based on the password given, have three columns be capable of updates, or all columns be capable of updates. Thanks for any help.

Paul

[Denburt]

You can do this by enabling or disabling the fields on a form according to the user in question. You would need to hide the database window and it does depend on how secure you would like this to be as to how much time and effort you want to put into it as to how you would or could lock things down.

[paulbrog]

It is a simple inventory list, I just don't want data getting erased on accident. I want to spend very little time, again just the three fields need updates the others need to be locked to prevent them being accidentally erased or modified. Thanks again.

[Denburt]

In order to lock down the fields in question you will need to know a little VBA this isn't really a point and click solution if that is what you are looking for.

However if you do know a little VBA then I will be glad to help as you can use MS Access security or if your in a domain you can use AD or you can use a windows login, it all depends on the security you are looking for. Basically when the form in question loads you would add an event such as Form_OnLoad and insert the appropriate code requesting the user info and then allow editing or not depending on the control.

[paulbrog]

I could try the VBA steps needed, we are not all going to be using this from the same domain, we have users on-site that will be remote connecting, and accessing a shared drive where the database is located. Security is not terribly important. I just don't want people messing with existing data, they have no real incentive to. I would only need one user, as all on-site people could use the same one.

[Denburt]

I would use a routine to cycle the controls on the form and either use the tag or the first three letters of the name of the control i.e SHW so that I would know what needed to be enabled for who etc. again there are many ways to achieve the end result. Setup MS Access security and once you have that.

Set wks = DBEngine.Workspaces(0)
GrpCount = wsp.Users(strUser).Groups.Count
 
strGrp= "Whatever you want"
 
chkFlag = False
For j = 0 To GrpCount - 1
    If wsp.Users(strUser).Groups(j).Name = strGrp  Then
        chkFlag = True
        Exit For
    End If
Next
 
For Each ctl In Me.Controls
if chkFlag Then
   If Left(C.Name, 3) <> "shw"  Then 
ctl.enabled = true
ctl.locked = false

That would leave the Three fields alone and you could essentially lock or unlock a field or fields etc. From there I think you would have a start let me know if you have any questions.