Perevent edits based on permissions

[liddlem]

Hi All
I have created an ACL table (access control list) where I have the fields UserID,FormName,CanSee and CanEdit.

When the user opens the db, the on open event gets the LoggedOnUserName and then check my ACL table to either prevent viewing or editing.

I have got the prevent viewing part working, but am having a small problem with the prevent edits.
I can set the property to prevent edits, but i need the user to be able to search records. the combo box is disabled if I use the following code.

MyCount = DCount("User_ID", "dbo_NPY_ACL_User", "(dbo_NPY_ACL_User.User_ID)=" & MyUserId & " AND ((dbo_NPY_ACL_User.CanEdit)=False) AND ((dbo_NPY_ACL_User.NavBtn_ID)=" & MyTab & ")")
If MyCount > 0 Then
Me.AllowEdits = False
end if

Any suggestions welcome.

 

[vbaInet]

What you need is cycle through each on the form and lock/disable them. First experiment with the Locked and Enabled properties of a textbox until you get the settings you want. Different combinations of Yes and No yield different results.

Once you get that, I'll tell you the next steps.

 

[liddlem]

Awesome thanks.
If have got it working (to a point) as follows.
(Im not at work anymore, so I'll try paraphrase it from what I recall)

for each ctl in MyForm
'(I think it was 106,109 and 111 for textbox, combo and checkbox)
if ctl.controltype = 106 or ctl.controltype = 109 or ctl.controltype = 111 then
control.locked = true
end if
next

and this locks the relevant fields

Then I set the allowedits properties to false on the subforms.
Finally, I change the search combo back using

me.combobox.locked = false

But for some reason, the combobox remains locked and I cant search for records.
I'll have a look at it again on monday.

 

[vbaInet]

Is the AllowEdits off? And do you have the combo box Enabled?

Here's a list of control constants. Use the constants instead of their numbers. Much easier to read:

http://msdn.microsoft.com/en-us/library/office/aa224135(v=office.11).aspx

 

[GinaWhipp]

That is because when you set it at the Form level *all* the controls are locked. I believe what vbaInet was suggesting was you Lock/Unlock the individual Controls not the entire Form.

Hmm, perhaps, if your Combo Box is UNBOUND, you could use something like this...
http://www.access-diva.com/vba6.html

Then adjust your code to read...

'whatever you have above this
MyCount = DCount("User_ID", "dbo_NPY_ACL_User", "(dbo_NPY_ACL_User.User_ID)=" & MyUserId & " AND ((dbo_NPY_ACL_User.CanEdit)=False) AND ((dbo_NPY_ACL_User.NavBtn_ID)=" & MyTab & ")")
If MyCount > 0 Then
[B][COLOR=red]    fncLockUnlockControls Me, True 'Locked[/COLOR][/B]
Else
[COLOR=red][B]    fncLockUnlockControls Me, False 'Unlocked[/B][/COLOR]
End If

 

[vbaInet]

http://www.access-diva.com/vba6.html

Saved... so I don't have to keep writing this over and over again

 

[GinaWhipp]

@vbaInet

Love that bit code! Works like a charm!

 

[liddlem]

Thanks guys - Got it working.
Gina - I was very close to your solution - just did not have it calling a function.

 

[GinaWhipp]

You're welcome... We're glad to help!