Read Only to a specific record 3

[Comp2008]

I have a Database (MS Access 2007) in a public folder; many people have access to it. Therefore, we encounter an inconvenience lately. When a record is filled out, I have no control over some other users to make changes after the deal is completed. In other words, once the deal is done, I want to avoid any further change from other users. Is any way to change a record to Read only? I’m familiar to change permissions to an object, but my intension is to change permissions to a specific record not to the whole Table or Query
Any help is greatly appreciated.
Thank you in advance

Comp2008

 

[PWise]

on form current
me.allowedits=not deal is completed

change deal is completed to whatever difines that a deal is completed.

 

[dhookom]

In addition to pwise's wise reply, you might want to post form questions in the Access Forms forum rather than the Access Reports forum.

Duane
Hook'D on Access
MS Access MVP

 

[Comp2008]

Pwise,

on form current
me.allowedits=not deal is completed
change deal is completed to whatever difines that a deal is completed. [\Quote]

I'm sorry for my ignorance but, could you be more specific? I don’t understand what you mean. The only thing I want to know is if I can change a record to “Read only” at any time, as we can change the objects’ permissions from Database tools tab. If not what would it be the easiest way to do it for anybody to use it.

On the other hand dhookom thank you for the observation.


comp2008

 

[dhookom]

pwise is suggesting you add some code to the On Current event of your form that sets the AllowEdits property based on the contents of a field in your form. For instance, if you have a field named CompletionDate on your form. If the field has a value, then set the AllowEdits property to False

Private Sub Form_Current()
    Me.AllowEdits = IsNull(Me.CompletionDate)
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Comp2008]

Thank you dhookom for making it more understandable. Now pwise’s suggestion makes more sense. Thank you both very much.

Comp2008

 

[Comp2008]

Private Sub Form_Current()
Me.AllowEdits = IsNull(Me.CompletionDate)
End Sub

I created a new field named CompletionDate as your example. Also, I added the code you gave me to the “On Current” event of my form, and I still not getting to set the AllowEdits=False to only one record based on the contents of the CompletionDate field in my form. On the other hand, when I set manually the AllowEdits property to “No”, it sets to all Records of my database. My intension is to Allowedits=No to only one specific Record. The code you gave me it makes sense, It causes the current record to be editable, regardless of the AllowEdits property settings, but for some reason, it doesn’t work. Am I doing something wrong?
Thank you in advance for helping,

Comp2008

 

[dhookom]

Your original posting stated "When a record is filled out". You have never provided any definition of what you mean by this. I assumed (probably wrongly) that you might have a bound control on your form named Completiondate that would answer this definition.

If you don't have this field then please define what you mean by "When a record is filled out".

Duane
Hook'D on Access
MS Access MVP

 

[Comp2008]

Ok, I’ll give you the whole picture. We have a Database (MS Access 2007) in a public folder. Some users are allowed to make changes and adjustments to the fields until accounting department decides that the whole transaction is completed. At that point adjustments are not supposed to exist. Therefore, no matter how many times we repeat how the procedures should be, there are always human errors. That’s why I’m trying to set a “not allow edit” property to a record after the transaction is done. Now, when you gave the code-example to add to the On Current event of my Form, I thought that it would be a good idea to add one more field called CompletionDate to fill out, to determine when the transaction is done or not. Also, based on if the field has a value, set the AllowEdits property to False.
I’m sorry if I was not clear before.

Comp2008

 

[dhookom]

I copied the code from my posting into a test form and it worked exactly as expected.

Can I assume your Access 2007 allows code execution?

What is your exact code and what are your results?

Duane
Hook'D on Access
MS Access MVP

 

[roccorocks]

I guess a good question to ask is if there is "one" record per form (or are you using a collection in the Continuous Forms?). If it is a Continuous form, you will not be able to conventionally lock down the record as read only. You will have to perform various checks (like in the Before_Update event and cancel a possible change). Or you can have unbound and do the binding on the fly (which I think can open up that possiblity, though not 100% sure).

Tim

 

[dhookom]

It shouldn't make any difference if the form is continuous or single. As the user navigates from one record to another, the record will be locked or unlocked.

One issue is how you want to handle immediate updates to other fields as soon as the CompletionDate is updated. The On Current isn't fired until the record is changed to a new record.

Duane
Hook'D on Access
MS Access MVP

 

[Comp2008]

I guess a good question to ask is if there is "one" record per form

To answer your question roccorocks, Yes, I have one record per form.

I copied the code from my posting into a test form and it worked exactly as expected.

Dhookom, If It works when you tested, That means that I’m missing something that I don’t see it. Let me tell you what I do: (step by step)
1. Open the form with the record I want to lock
2. Change to Dedign view
3. Set the AllowEdits property on the form to No. (from Property Sheet, Data tab)
4. Set the On Current property (Event tab) of the form to the following event procedure:

 Private Sub Form_Current()
    Me.AllowEdits = IsNull(Me.CompletionDate)
End Sub

5. Save the form and open it in Form view.
Note: When the AllowEdits property is set to Yes, (step #3) the event procedure has no effect; I am still able to edit fields on the form. Now, when set the AllowEdits property to No, (step #3) It affects to all records; I’m not able to edit fields on all the records. And, I want to set the AllowEdits to false to only one specific record not to all of them.

I thought changing a field value programmatically causes the current record to be editable or not, regardless of the AllowEdits property settings.

Regarding the question:

Can I assume your Access 2007 allows code execution?

I don’t know how to test it.

Comp2008

 

[dhookom]

The code will set the AllowEdits property. You don't need to waste your time on #3.

In your most recent response, you didn't provide any information about the value of the CompletionDate field. This is the value that switches the AllowEdits from true to false. It is probably the single most significant piece of information in debugging this issue.

To test if you are allowing code to run, change the code

Private Sub Form_Current()
    MsgBox "Can you hear me now?"
    Me.AllowEdits = IsNull(Me.CompletionDate)
End Sub

If you see the msgbox, then the code is running.

Duane
Hook'D on Access
MS Access MVP

 

[Comp2008]

Yes, you are right… I Added a field called CompletionDate and, I entered a value once, on only one record, for testing purpose.
Regarding the new code you gave me, to test the code execution:

Private Sub Form_Current()
MsgBox "Can you hear me now?"
Me.AllowEdits = IsNull(Me.CompletionDate)
End Sub

I did it and there is no message box showing the coded question. It should be obvious to see it right?
If that is the case, is there a way to make the code execution run?

Comp2008

 

[dhookom]

I believe there is something in the Office Button menu that enables code. When I open an appication that has code, I think I get prompted to allow the code. There might even be some type of bar under the ribbon at startup.

Duane
Hook'D on Access
MS Access MVP

 

[Comp2008]

Ok, I’ll see what I can do. If you find something specific, please let me know. I really appreciate your help.

Comp2008