×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Set and Unset Read only based on user.

Set and Unset Read only based on user.

Set and Unset Read only based on user.

(OP)
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

RE: Set and Unset Read only based on user.

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

CODE

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.

RE: Set and Unset Read only based on user.

(OP)
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

RE: Set and Unset Read only based on user.

(OP)
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

RE: Set and Unset Read only based on user.

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:

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

CODE

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 smile

---- Andy

There is a great need for a sarcasm font.

RE: Set and Unset Read only based on user.

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/article/set-or-ch...

RE: Set and Unset Read only based on user.

(OP)
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

CODE -->

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

RE: Set and Unset Read only based on user.

First, is your UserID 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:
.AllowAdditions = False
.AlowDeletions = False
.AllowEdits = False


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

RE: Set and Unset Read only based on user.

(OP)
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

RE: Set and Unset Read only based on user.

(OP)
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. "

CODE -->

blnIsAdminUser = Len(DLookup("UserID", "AuthorizedUsers", "UserID = '" & User & "'")) 
". I am not sure what this means. Can you help?
.......................................................................................................................................................
This statement works in another application where the code looks like this:

CODE -->

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

RE: Set and Unset Read only based on user.

"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:
IsAdminUser = Len(DLookup("UserID", "AuthorizedUsers", "UserID = '" & User & "'") & "")

Since you cannot get the Len out of NULL, just add an empty string to it.

Give it a try, report back. smile


---- Andy

There is a great need for a sarcasm font.

RE: Set and Unset Read only based on user.

(OP)
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.

CODE -->

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

RE: Set and Unset Read only based on user.

NZ solution is just fine, and it works, which is even better.
But I would be very explicit and add:

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

(I hope the zero is in the right place)


---- Andy

There is a great need for a sarcasm font.

RE: Set and Unset Read only based on user.

(OP)
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

RE: Set and Unset Read only based on user.

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:
varResult = Nz(varFreight, "No Freight Charge")


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


---- Andy

There is a great need for a sarcasm font.

RE: Set and Unset Read only based on user.

(OP)
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

RE: Set and Unset Read only based on user.

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


---- Andy

There is a great need for a sarcasm font.

RE: Set and Unset Read only based on user.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close