Locking records of a form after approval

[Anil Bagga]

I have the forms of my DB in a fe file, which is common for all users - there are not user logins till now as there are very few users



Certain records need approval after creation and I would like to lock them for editing, after approval. What is the best way to do it? Maybe I could have a version of the form in my computer, where I have Approval field, not visible to the users, which I can use for approval of the records. If I say Yes, the record gets locked till I unlock it

[June7]

Is this a split db with Access backend? If you really want to have this tight of control over permissions to edit records, should probably convert to SQLServer or similar database platform.

You would either have to not display those records to users by filtering or use code to disable/lock form in total or each control. Conditional Formatting can enable/disable tetboxes and comboboxes by record.

Of course, none of this will prevent users opening tables directly and doing whatever they want.

[CJ_London]

Presumably you have a field to indicate a record is approved - name of the approver? Date of approval? Something else?

So could be as simple as some code in the form current event along these lines

Me.allowedits=isnull(me.dateapproved)

I do hope your comment re fe file being common to all users does not mean they are all using the same fe file

[Anil Bagga]

Hi Ajax

Unfortunately they are using the same fe file . I intend to make another form for the approver with this code below for locking and the other users will get a display of the status approved/not approved

Will test this code.

Thank you

Anil

Presumably you have a field to indicate a record is approved - name of the approver? Date of approval? Something else?

So could be as simple as some code in the form current event along these lines

Me.allowedits=isnull(me.dateapproved)

I do hope your comment re fe file being common to all users does not mean they are all using the same fe file

[Welshgasman]

Hi Ajax

Unfortunately they are using the same fe file .

Anil

Best to give them their own FE ASAP, before you have real problems.

[CJ_London]

Unfortunately they are using the same fe file

agree with WGM - give each user their own copy before you hit a corruption problem that can't be resolved. The fact it hasn't happened yet is just luck on your part.

[Anil Bagga]

Everyone uses a copy of the same fe file, not the same file. Sorry for the lack of clarity.

I intend to have a new form in the fe file of the Approver which will allow locking of the records using the code suggested by Ajax. The other users will be able to view if the record is approved or not approved

[Anil Bagga]

Just thinking loud, can we not have a password enabled locking of the record. This way I don't need a new form and the password will only be with the approver!

[June7]

Password? Sure, and my earlier comments still apply.

Or match the approver in record with Windows username login.

Each user should run their own copy of FE. This assumes there can be multiple simultaneous users.

[Gicu]

Anil, be aware that if you set

Me.AllowEdits = False

then you will not be able to use unbound controls (such as combos) to search\navigate records. If you do need that kind of functionality on the form you can set its RecordsetType property to Snapshot or 2.

If YourConditionForLockedRecord=True Then Me.RecordsetType = 2

Cheers,

[Anil Bagga]

Hi Vlad

Now this gets complicated and beyond my depth of understanding. I will try this on the weekend and will revert either way - success or failure

Hi June7
I dont want to to go to the login route with 4 or 5 users - I dont have the skills and so far this has worked. Fingers crossed.

I have a master fe file. All users copy this to to their computers and run it from there. They dont have access to the tables in the fe file (No navigation pane). Everyone uses this fe file copy which is on their desktop. When new versions of fe file are made, they simply start using the new fe file and discard the old one. This may not be the best way to do it but end justifies means I guess. Have to work within my constraints

[Anil Bagga]

Hi Vlad,

The date field in the form is "AuditDate"

I Added the following code in the current event of the form

Me.AllowEdits = IsNull(Me.AuditDate)
If IsNull(Me.AuditDate) = True Then Me.RecordsetType = 2

The record does get locked but the combo box search in the form header does not work. Also in case the auditor wants to unlock the record and edit it, even he cannot unlock the record! Scrolling records from the bottom of the form is not possible!

What was desired is a process for the auditor to lock and unlock records on his version of the fe file and the other users will see the audit date as a view only field for them to understand that the record has been audited.

[Gicu]

Anil,

You should not use both (AllowEdits=False and RecordSetType=2). Having AllowEdits=False will disable the functionality of the unbound search combos. Now the question is how would the program know you are an "auditor"? Do you have a user table with an Auditor (yes\No) field and a way to check the current user against that? Do you use some sort of form-based password scheme where you can "unlock" the form once a valid password is entered?

Cheers,

[Anil Bagga]

Hi Vlad

I propose to have a separate fe file for the auditor. Barring this there is no other user authentication set currently for different users.

Everyone copies the master fe file on their computers and runs it from there.

Is it possible to have an audit authentication in this scenario? Perhaps we can save a password in a table (for ease of change if needed ) and have a toggle button to lock and unlock the record with a password authentication?

[Gicu]

Hi Anil,

To have a separate file for the auditor(s) will involve duplicating all your changes, even if unrelated to the "auditor" functionality. So I would strongly suggests having only one version of the front-end and implementing some sort of user management; the easiest is as you suggested having one or more passwords saved in a (hidden or custom system) table and simply prompting for the password when users want to edit\add\delete fields\records...

Or you could have a custom access level security system in place where you could set different "levels" of allowable access (read-only\data-entry\manager=delete allowed, etc.) and for each form you could set each control properties accordingly and\or at the form level (Allow Edits, Allow Deletes ,Allow Additions). There are many examples on the web for this, I have developed my own version over the years (http://forestbyte.com/ms-access-util...access-levels/), but there are many more.

Cheers,