asked on

Triggering a pessimistic record by user name or ID

Hello Folks,

Can you please advise on the following.

I have an Audit Form which displays details of audit checks that where carried out on a particular process. On this Audit form the name of the creator of the audit record is displayed along with the name of the person who person who carried out the process exercise.

The form called, 'frmNewAudit' has it's record source bound to a table called 'tblAudit'. Contained in the 'tblAudit' table along with various other fields is the name of the auditor, field name 'Auditor' and processor, field name 'Processor'.

My question is, can you advise how I can trigger a pessimistic record lock so that only the Auditor (the original creator of the record) can edit the record and no one else?

I am aware that this procedure will also require the inclusion of the Public Function fOSUserName function call or something similar.

Sincerely

Dale

Scott McDaniel (EE MVE )

You'd have to (a) create some form of login process, as you mention and then (b) check the login credentials against the Auditor field when the user tries to open the record. You could do this in the Open event of the form, which can be cancelled.

If Auditors can only view their own records, then you should introduce a process where records are filtered. For example, you could use a query as the source for your form, and filter that query based on the login data.

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale James

ASKER

Thank you everyone for your responses to this question. I shall apply each suggestion later today and I shall feedback my results.

Your quick responses are very appreciated.

Thanks again.

Sincerely

Dale

PatHartman

Hi Dale,
How are you doing?

Dale James

ASKER

Hello Pat

Yes, I have applied the suggestions provided and had to extract elements of advise provided by yourself and Dale. I found that the AllowEdits property was not the best option to use and had to drive the edit and non edit status via the FOSUsername with the condition of allowing the controls collection to have either a True or False Enabled status.

So far so good.

Thank you once again for your assitance.

Sincerely

Dale

PatHartman

That was an awful lot of work when catching the change at the end accomplishes the same thing which is the code I posted.

Please close the question and accept your own solution since you didn't use mine or Dale's

Dale James

ASKER

Thanks Pat...as I was working on the original requirement the scope changed a bit and it was to ensure that no change was possible if username did not match but there was a requirement to allow all users to have the ability to search for further records whilst the locked record was displayed on the form.

I did try to credit both you and Dale for your assistance but it wasn't being retained as both you and Dale did provide appropriate answers to the original question. Sorry, am new to the site, just need to get into the routine of how it works.

Thanks again everyone!!!

Sincerely

Dale

PatHartman

You're welcome.

That is EXACTLY what I warned about:

setting AllowEdits to False might prevent certain search features from working.

I also gave you a second warning. You probably haven't run into this yet, but you will.

You also might need to consider the need to override this rule. Can a supervisor update a record created by someone else? What happens if that person leaves the company?

It isn't necessary to award participation points to me if you didn't take my advice. So, unless the others object, just accept your own answer.

Dale James

ASKER

Yes, you are correct Pat...but it was only by applying the code was I able to to see it occurring in practice which allowed me to understand your advise more. I also addressed the necessity to allow the locked status to be overwritten as well. So far so good. As we say...everyday is a school day.

Thank you Pat!!

Dale James

ASKER

So as a closing summary of this original question. I applied a selection of advise provided by Dale and Pat with an inclusion of running a controls collection in order to lock those controls which were not be available to anyone else except the original audit creator or appointed administrator. In addition the exclusion of specific search controls was coded to allow additional searches to be made from the location of the locked record.

Once again....my thanks to Scott, Dale and Pat for their contribution.

PatHartman

You're welcome. I would have posted code to lock the controls but as you explained the situation, that wasn't necessary. I use a generic procedure that takes the form object as input so it works for All forms that call it.