Set and Unset Read only based on user. 1

[puforee]

Is there a way to set DB read only for certain users and remove read only for others. I have a table of authorized users allowed to make data changes.

Thanks,

John

 

[Andrzejek]

You could use a DLookup Function to determine if your user has read-only privileges or not, and act appropriately:

If Len(DLookup("User", "TableName" "User = '" & UserName & "'")) = 0 then
    Me.AllowAdditions = False 
    Me.AllowDeletions = False 
    Me.AllowEdits = False 
Else
    Me.AllowAdditions = True
    Me.AllowDeletions = True
    Me.AllowEdits = True
End If

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Andy, thanks. Now, I will have several forms/queries/tables, where would I put this code so when the DB is open it will run? Also, will the Allow.... code work on all items in the DB or do I have to add it to each Form?

Thanks,

John

 

[puforee]

Additional thought. When the DB opens it will start a Switchboard Form. Can I attach your code to the Open Event for the switchboard? And, the same question as before, if it runs the code does "Me.AllowEdits etc codes", apply to all forms? The control of the forms is what I am really interested in.

Thanks again Andy

John

 

[Andrzejek]

I would try it first on one Form just to make sure it will work the way you want.
If it does, you may set up a little Public Sub in standard Module:

Public Sub MakeFormReadOnly(frmForm As Form)

With frmForm
    If Len(DLookup("User", "TableName" "User = '" & UserName & "'")) = 0 then
        .AllowAdditions = False 
        .AllowDeletions = False 
        .AllowEdits = False 
    Else
        .AllowAdditions = True
        .AllowDeletions = True
        .AllowEdits = True
    End If 
End With

End Sub

And then have this in every Form Load or Open event:

Call MakeFormReadOnly(Me)

PS. I would not be surprised if there is another / better way to do it...

BTW - Here is a little article about what you are trying to do. Almost the same approach

---- Andy

There is a great need for a sarcasm font.

 

[strongm]

Microsoft removed user-level security in more recent versions of Access. It is still possible to use it if you are prepared to use older Access file formats, at which point 'Manage Users & Permissions' magically appears under File->Info

At which point the following article may be of use to you:

https://support.office.com/en-gb/ar...r-higher-0c6a10e7-966f-44f4-864e-5d2ef79439fa

 

[puforee]

Andy, I like what I see but I think I did it a little wrong. I put this code in.

Public Sub MakeFormReadOnly(frmForm As Form)
'This code reads the userID and checks it against the a table. If there is a match
'The form will allow additions, deletions and Edits.

DoCmd.Maximize
Dim blnIsAdminUser As Boolean


blnIsAdminUser = Len(DLookup("UserID", "AuthorizedUsers", "UserID = '" & User & "'"))


With frmForm
If blnIsAdminUser = True Then
.AllowAdditions = True
.AlowDeletions = True
.AllowEdits = True
Else
.AllowAdditions = False
.AlowDeletions = False
.AllowEdits = False
End If
End With

End Sub[/code]
..............................................................................................................................................
I then set OnCurrent Event for the Form to

Private Sub Form_Current()
    Call MakeFormReadOnly(Me)
End Sub

When I run the code and it Calls the pubic code it takes the proper "If" path but when it is done I cannot edit anything on the form. It seems the "Allows" don't do anything.

Can you see where I might be going wrong? The form I am working of is " Personnel - Accesses".

Anxious for your reply. I will now look your "little article"

Thanks,
John

 

[Andrzejek]

First, is your [tt]UserID[/tt] field a numeric or text field?

"When I run the code and it Calls the pubic code it takes the proper "If" path..."
Great! So I assume you run thru: [tt]
.AllowAdditions = False
.AlowDeletions = False
.AllowEdits = False[/tt]

"...but when it is done I cannot edit anything on the form. It seems the "Allows" don't do anything."

So if I am right (above), the code works like it should and does not allow you to Edit (or Add, or Delete) So it works.


---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Andy, The field is text, because our UsrId's are alph-neumeric, and resides on the open form. The path that the code takes is the "True" return for the "If" part of the code.

This means, since I am logged on, that I should have edit access to the form. The "blnIsAdminUser = Len(DLookup("UserID", "AuthorizedUsers", "UserID = '" & User & "'"))" statement in the code checks my log on userID against a table of authorized users. It returns True for me when I am signed in

I use MsgBox's at several points in the code when testing the to ensure everything was going the correct way. Everything looks good testing this way.

The Form is initially opened with all three "Allows" set to False, this is the way the form is stored. When the code is run it should set all the "Allows" to True because it validated I am a person who should be able to add/delete/edit data.

Does this answer your questions?

Thanks,

John

 

[puforee]

Andy,
I found the problem I was having. It was some settings I had that did not directly relate to the code. After I reset those settings everything worked and I could edit my form.

This I tried removing myself from the AuthorizedUsers table expecting I would not be able to edit the form. When I ran the code again I received an error:

Run-time error '94':
Invalid use of Null

When I select Debug this code was highlighted. "[highlight #FCE94F]

blnIsAdminUser = Len(DLookup("UserID", "AuthorizedUsers", "UserID = '" & User & "'"))

[/highlight]". I am not sure what this means. Can you help?
.......................................................................................................................................................
This statement works in another application where the code looks like this:

    DoCmd.Maximize
    Dim blnIsAdminUser As Boolean
    Dim cntr As Control

   
   'MsgBox User
   blnIsAdminUser = Len(DLookup("UserID", "MetUsers", "UserID = '" & User & "'"))
   'MsgBox "The variable is" & " " & blnIsAdminUser
   'MsgBox blnIsAdminUser
   For Each cntr In Me.Controls
    If cntr.Tag = "ADMIN_CNTR" Then
        cntr.Visible = blnIsAdminUser
    End If
   Next cntr

Why would it not work it the one you and I have been working on? This code at the bottom is a Private Sub attached to a specific form. Where you had me put ours in a Public Sub.

Thanks,

John

 

[Andrzejek]

Invalid use of Null" - I think your DLookup function returns NULL since your UserID was not in the table. You can either:
1. Replace DLookup with DCount function to determine if there is/is not a record with the UserID. If there is NOT, you would get 0 records, which would be FALSE, or
2. Modify your DLookup to be:[tt]
IsAdminUser = Len(DLookup("UserID", "AuthorizedUsers", "UserID = '" & User & "'") [highlight #FCE94F]& ""[/highlight]) [/tt]
Since you cannot get the Len out of NULL, just add an empty string to it.

Give it a try, report back.



---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Andy,

I thought I sent a response Sat or Sun. I got it to work, but as I see above,not exactly by your method. Check out this code that works...I used NZ.

blnIsAdminUser = Len(Nz(DLookup("UserID", "AuthorizedUsers", "UserID = '" & User & "'")))

Do you see any problem with my solution (with help from google)? Is either solution better than the other. I did not try yours but I can.

Thanks,

John

 

[Andrzejek]

NZ solution is just fine, and it works, which is even better.
But I would be very explicit and add:
[tt]
blnIsAdminUser = Len(Nz(DLookup("UserID", "AuthorizedUsers", "UserID = '" & User & "'"))[highlight #FCE94F], 0[/highlight])
[/tt]
(I hope the zero is in the right place)


---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Andy,
Thanks for staying with me on this. I still have more work to do on the DB but I will pay strict attention to user testing. I will also test with the 0 you are recommending. What is its purpose so I know what should happen.

Thanks,

John

 

[Andrzejek]

NZ function is this: take a field from DB and return its value. But if NULL is returned, give me X instead. I think by default NZ returns 0, but it could return other 'stuff', like:[tt]
varResult = Nz(varFreight, "No Freight Charge")[/tt]

But if you can, you should explicitly define what is returned, IMHO


---- Andy

There is a great need for a sarcasm font.

 

[puforee]

OK Andy back again. Is there a way to run the code against the "AuthorizedUsers" Form when I open it? It is tied to the Authorized User Table. I only want Authorize Users to be able to modify that table. If I can, what event would I use to call the code. On current does not work. Or, maybe it works but I need to refresh the Form? But I think that would just rerun the code. Bye the way, everything else is working fine.

Thanks again,

John

 

[Andrzejek]

According to this place, events in form 'fire' in this order:
[ul]
[li]Open[/li]
[li]Load[/li]
[li]Resize[/li]
[li]Activate[/li]
[li](GotFocus)[/li]
[li]Current[/li]
[/ul]
so I would try either Open or Load and see ...


---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Thanks Andy, I will try it today. I will let you know the results.