Locking records/form (1 Viewer)

tmyers

Well-known member
Local time
Today, 11:35
Joined
Sep 8, 2020
Messages
1,090
I am not too sure which sub-forum this should go under, but since I think it would be done on the form itself, I will place it here.

I have been reading various threads on here and elsewhere about record locking. It seems most of them are just for the purpose of preventing multiple users from editing the same record at the same time. That is not quite what I am after however.

Is there a way to have an entire set of records to become locked and stay locked until an admin unlocks them? I have not setup a login system just yet, but I have messed with it before and laid the ground work for it.

Essentially one concern a user and the boss brought up is maintaining an "authentic" copy of a quote, that way if say a salesman comes in later and tries to mess with/edit it, they can't. This would be to ensure we always maintain an original copy of a quote. This was never a problem with excel, since the person who did the quote would always have the original file.

In a perfect world, I would be able to allow certain users the ability to click a button and place a lock on the records that only a few others could click to undo. Another set of users could still view the records, but the button would be disabled for them thus not allowing them to unlock them. They could however click the create revision button, cloning the record set into a new, replicated quote. That would preserve the original, but give them the ability to still make changes if they need it.

Is something like this doable for a novice such as myself? As I said higher up, I have messed with a login system once before, but didn't think it would be needed so never fully developed it, let alone go further and granting certain levels of permissions.
 

isladogs

MVP / VIP
Local time
Today, 15:35
Joined
Jan 14, 2017
Messages
18,186
As long as users have no access to tables or queries then this is simple to do.
Just lock your form from editing depending on the user role.
To do so set AllowEdits=False in the Form_Load event for standard users

To prevent users accessing the underlying tables or creating queries you need to apply security measures such as:
a) hide the navigation pane & ribbon
b) use an ACCDE and rename as ACCDR (or only use Access runtime)
c) disable Access options from the file menu

BTW tables CAN be locked but its quite complex to do and would prevent editing bering done by the admin user when required
 

tmyers

Well-known member
Local time
Today, 11:35
Joined
Sep 8, 2020
Messages
1,090
As long as users have no access to tables or queries then this is simple to do.
Just lock your form from editing depending on the user role.
To do so set AllowEdits=False in the Form_Load event for standard users

To prevent users accessing the underlying tables or creating queries you need to apply security measures such as:
a) hide the navigation pane & ribbon
b) use an ACCDE and rename as ACCDR (or only use Access runtime)
c) disable Access options from the file menu

BTW tables CAN be locked but its quite complex to do and would prevent editing bering done by the admin user when required
I think what I may try is have a button that is enabled for certain users, disabled for others. Users it is enabled for can click it and set allowedits=false (and click again to set to true), while for others that button is disabled. So once a "admin" locks it, those without permission can't unlock it.

One question though, if a user sets the allowedits to false, will that also lock others users using the same form but in different records from making edits?

The people I am attempting to "lock out" I still want to have the ability to make their own quotes and such. I just dont want them having the ability to mess with records already created.
 
Last edited:

tmyers

Well-known member
Local time
Today, 11:35
Joined
Sep 8, 2020
Messages
1,090
I am not having much luck so far. It seems if I set allowedits to false, it locks the form to everyone.
Going to have to rethink this a little bit.
 

isladogs

MVP / VIP
Local time
Today, 15:35
Joined
Jan 14, 2017
Messages
18,186
I did say depending on user level...

The following assumes users login, either to your app (or to Windows).
1. Add a number field called UserLevel to your tblUsers and assign values to each user e.g. 1 for standard users, 2 for admin users, 3 for developers.

2. Add a function GetUserLevel similar to this to a standard module

Code:
Function GetUserLevel()
  GetUserLevel=Nz(DLookup("UserLevel","tblUsers","UserName = '" & Environ("UserName") & "'"),0)
End Function

3. Add code like this to your Fom_Load event

Code:
Private Sub Form_Load()
    
    Select Case GetUserLevel
    
    Case 0,1
    Me.AllowEdits=False
    
    Case 2,3
    Me.AllowEdits=True
    
    End Select
    
    End Sub

Modify as appropriate...
 

tmyers

Well-known member
Local time
Today, 11:35
Joined
Sep 8, 2020
Messages
1,090
Wow I super over complicated it compared to that.
Thanks Isla! By the way, I can't tell you how many samples I have come across from you that I ended up using for my project. Your stuff has been super helpful.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:35
Joined
Oct 29, 2018
Messages
21,358
The people I am attempting to "lock out" I still want to have the ability to make their own quotes and such. I just dont want them having the ability to mess with records already created.
Hi. Assuming a quote has to be approved before it's final, then how about simply adding an Approved field that you can use to make sure the final/approved quote can't be edited? Just a thought...
 

tmyers

Well-known member
Local time
Today, 11:35
Joined
Sep 8, 2020
Messages
1,090
Hi. Assuming a quote has to be approved before it's final, then how about simply adding an Approved field that you can use to make sure the final/approved quote can't be edited? Just a thought...
I sort of already have that. Rather than approved, I have "Completed".
 

isladogs

MVP / VIP
Local time
Today, 15:35
Joined
Jan 14, 2017
Messages
18,186
Wow I super over complicated it compared to that.
Thanks Isla! By the way, I can't tell you how many samples I have come across from you that I ended up using for my project. Your stuff has been super helpful.
You're welcome. Always good to see the examples being used

BTW if users login to your app, instead of using Environ, get the user name from the login screen , possibly using another function GetUserName.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:35
Joined
Feb 28, 2001
Messages
26,999
@tmeyers - in general what you want to do is accomplished using methods that Colin (isladogs) and theDBguy have suggested. But here is the reasoning behind the scenes.

The general rule is to protect something, you need to be able to MARK something in a way that your app will honor. If your users can directly open tables or queries, you have NO repeat NO security. If you have it so they can only see forms and reports, then you have control over what they can see and what they can do. If you have a "PROTECTED" field (Yes/No) on a record and only a supervisor can set it to NO (expose it), then once it is protected, your forms can see it and know to not touch it. After that, it is a matter of what roles you allow to make changes and who gets those priveleged roles.

If you want selective protection, you have to build in your own selectivity. How dp you do that? A myriad of ways exist. But all of those ways start with a controlled environment where users DO NOT get to see everything. In a wide-open, uncontrolled environment, I am reminded of a quote from John Milton: All hope abandon, ye who enter here.
 

tmyers

Well-known member
Local time
Today, 11:35
Joined
Sep 8, 2020
Messages
1,090
@tmeyers - in general what you want to do is accomplished using methods that Colin (isladogs) and theDBguy have suggested. But here is the reasoning behind the scenes.

The general rule is to protect something, you need to be able to MARK something in a way that your app will honor. If your users can directly open tables or queries, you have NO repeat NO security. If you have it so they can only see forms and reports, then you have control over what they can see and what they can do. If you have a "PROTECTED" field (Yes/No) on a record and only a supervisor can set it to NO (expose it), then once it is protected, your forms can see it and know to not touch it. After that, it is a matter of what roles you allow to make changes and who gets those priveleged roles.

If you want selective protection, you have to build in your own selectivity. How dp you do that? A myriad of ways exist. But all of those ways start with a controlled environment where users DO NOT get to see everything. In a wide-open, uncontrolled environment, I am reminded of a quote from John Milton: All hope abandon, ye who enter here.
I see.
I do feel I succeeded in never allowing direct access to queries or tables. I have disabled all ways (that I could think off) that would allow users to get to them. Only I can currently access those.

Granted the way I used could be spoofed, but no one in my work place is good enough from a technical point to be able to do it. Except MAYBE one person.
 

Users who are viewing this thread

Top Bottom