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
Dale JamesTherapistAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
Another option is to verify the credentials in the Form's BeforeUpdate event.  Depending on the form, setting AllowEdits to False might prevent certain search features from working.
Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.txtAuditor = fOSUsername() then
    Else
        Cancel = True
        Me.Undo
        Msgbox "Only the record creator is allowed to update.",vbOKOnly
        Exit Sub
    End if

End sub

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?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
Dale FyeConnect With a Mentor Commented:
I usually put that type of check in the Form_Current event, assuming your "Auditor" field is the Windows UserID then it would look something like:

Private Sub Form_Current

    if me.txtAuditor = fOSUsername() then
        me.AllowEdits = true
    Else
        me.AllowEdits = false
    End if

End sub

Open in new window


or, if you want to prevent anyone else from seeing those records, you could change the Recordsource of the form so that it looks something like:
SELECT * FROM tblAudit WHERE Auditor = fOSUserName

Open in new window

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Dale JamesTherapistAuthor Commented:
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
0
 
PatHartmanCommented:
Hi Dale,
How are you doing?
0
 
Dale JamesTherapistAuthor Commented:
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
0
 
PatHartmanCommented:
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
0
 
Dale JamesTherapistAuthor Commented:
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
0
 
PatHartmanCommented:
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.
0
 
Dale JamesTherapistAuthor Commented:
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!!
0
 
Dale JamesTherapistAuthor Commented:
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.
0
 
PatHartmanCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.