Question How do I make a record read only

[Sarah]

Hi

I have created a form that our sales team can use to enter sales/customer information. untill the work is fully approved the user is able to go in and amend any of the fields.

once approved backup documentation has been received by me I want to be able to make the record read only so the user cannot go in and amend or update the form, but can still review it, print it etc. I was thinking a checkbox of some sort.

is this possible?

I have searched the many posts on here and cannot see anything specific that jumps out, so I'd very much appreciate any replies on this.

kind regards

Sarah

 

[Guus2005]

if your recordset was based on a query, make it a "group by" query. Then your data is readonly.

HTH

 

[RuralGuy]

A CheckBox yes, but only the admin can uncheck it and you watch for it in the Current Event of the form and set the form properties accordingly.

 

[Sarah]

Hi

your information is gratefully received but I am so new to this you wouldn't believe it. The database is inherited from our US subsiduary and believe it or not this system is not supported by the company - so I'm very much on my own.

The form is built around a table, not a query. It will be app 500 records in total by the time it is completed. I need to be able to 'lock' on a line by line basis a record to stop the sales team making changes that may impact our SOX controls, as and when the approved paper documents get to me.

Please reply as if talking to a complete novice in this application.

much appreciated again.

Sarah

 

[RuralGuy]

Forms can be displayed as Single Record display or as a Continuous Form. How is your form set up. BTW, it is easy to create a query of a table that includes all of the fields and records and then set the RecordSource of a form to that Query. The form will not know you have even done this.

 

[Sarah]

Hi RG

I think I better quit while I'm ahead - I wouldn't know how to find whether this was a single record dispay or a continuous form - I'm way out of my depth on this.

Maybe I didn't ask the question in the right way.

I only want the data (for each record) to become read only, when I have received the back up and to stop them making any further ammendments. Until that time they can have full access to their individual records to change and edit anything.

Sarah

 

[RuralGuy]

It is not that difficult to work with. What version of Access are you using?

 

[Sarah]

Hi

I suppose the one at work is access 2000 but my home computer has access 2007 - we should base it on the one at work as its the sales force that are going to have to use it.

thanks

 

[RuralGuy]

The interface is quite a bit different so that's why I was asking. To find out how the form is displayed you look at the Format tab of the Properties Sheet for the form in design mode and the Default View is the value we need. Can you see more than one record at a time? How do you get from one record to the next?

 

[SimonB1978]

Hi Sarah,

I already did something similar to that. You can add a (Yes/No?) field to your table. You (the admin) will be the only person that can access this checkbox. In the Current (I think it's that one) event of the form you check the state of that field. If it is checked (backed-up) then you set all the controls on the form (text box, combo, check box, etc) as disabled. That way users can see the info without being able to modify it. If not checked you enable the controls.

I think it would be quite easy to modify the current form to achieve this.

Simon B.

 

[RuralGuy]

The idea is the right one but all you need to do is set:
Me.AllowAdditions = NO
Me.AllowDeletions = NO
Me.AllowEdits = NO
...if the flag is set. Otherwise set them to YES.

 

[Sarah]

Ok - this is starting to make sense - I'm attaching a screen shot of the form and it's showing all the boxes you mention currently set as 'Yes' there is already a box, which is currently visable but which I can hide, on the form, labeled 'check if current budget is committed' in the table it's a yes/no. I manually check this for now but it's only been me using it.

So do I leave the box checked, change Allow, Additions, Deletions & Edits all to 'NO' and save the form. How does it know that if the 'committed budget' box subsequently gets un checked, that it allows things to be changed?

Thanks both..

btw the system here at work is Access 2003

 

[SimonB1978]

Hi Sarah,

You will need to go to the VBA code, in the Current Event and enter something like this:

Me.AllowAdditions = Not checkbox_name.Value
Me.AllowDeletions = Not checkbox_name.Value
Me.AllowEdits = Not checkbox_name.Value

That way each time the form is "refreshed" with another record it will set the properties according to the state of the checkbox.

Simon B.

 

[gemma-the-husky]

but all this asumes the database is secured

ie do you have to enter user names and passwords to start the database? can anyone see the tables and queries etc? if not, this is all just cautionary but wont stop a knowledgeable person changing things

but what if you REALLY DO want to change some data - then you either need code to determine who is using the database ... or you need another form that DOES let you make edits, and then you need to restrict other users from this form (and thats the point about ADMINS that RuralGuy made above)

but please note, if you are new to all this, this is non-trivial stuff

 

[Lightwave]

Hi Sarah,

I'm repeating some of the stuff already said but I went away and wrote you a fairly lengthy explanation I can see you are at the beginning of your learning curve here and things may sound a bit scary don’t worry you probably won’t get this on your first reading. You’ll probably have to read it over a few times try and type it in get it wrong review it and then finally get it sorted. Stick with it everyone starts out like this!!!! Gemmas and Rural G have valid points but sounds to me as if this is not a large database and what you inherited was put together in a kind of amateurish way as such I'm guessing that you don't have to enter user names and passwords to get into the database.(I may be wrong) In which case you should be able to progress with the following.

Within your attached jpeg you've obviously recognised that the box on the right shows pre-defined properties of whatever is on your form visible or not visible. If you change any of the properties you refer to "addition, deletions and edits" to no then your statement that a user will not be able to edit the form is correct.

You are also completely correct that changing your tick box on the form (completed record field) will not change these settings. (Your thinking like you need to think)

What you might not have discovered is that each form not only has a set of properties associated which you get into by going to properties of selected field it but it can have (but not always) a Visual Basic Code sheet associated with it as well.

There are several ways of getting into the VB code sheet. One way is to go into the properties again and using your cursor select an individual property. Note some BUT not all properties can have code attached to them. If a property can have code when you place your cursor in the property a box will appear on the same line to the right with dots within it. Hit that button and you will get through to the code sheet via a interim window which asks expression/macro/or code you need to select code builder. We will come back to this but read the following next.

In order for your field which contains the status of the record (completed or otherwise) to change according to whether the completed field is ticked you will need to create a basic switch in code.

A verbal description of this switch will go like this

If Completed field is set to yes then

Change the specific properties of add/delete and edt of that record you are on to No

In Rural Guys example he is giving an example of how in code you would set the properties for a specific record to no. For example..
Me.Edit = No

Breaking this down and looking at this indvidually "Me" relates to the record which the database is currently on "Edit" relates to the property of the field which is on the form for the selected record and the "= No" indicates to Access that the property should be set to no irrespective of what it was previously set to.

So this is a simple line of code that sets that field on that record so it cannot be edited.

The next thing you will need to get your head around is the principle that properties can relate to more than one thing.
For your question and to keep things simple recognize that In your case all you need to understand is that properties can relate to either individual fields or alternatively relate to forms as well. Properties can relate to loads of other things but you won’t need to use thess so don’t concern yourself about this at present.

You are wanting the record or whole form to not be editable once the completed field tick box has been completed. So you need to be within the PROPERTIES OF THE FORM

Get to this as follows.

Open your form in Design Mode
Use your mouse to select the small grey box in very top left of the form (a black centre will appear when this is selected)
You can then only access the properties by hovering your cursor over that small black selected box and hitting the right mouse key and then going to properties.

The properties of the FORM should now appear
(You can check that you are in the correct place as the properties window should have FORM in the top Bar)
Later Edit : I note that your jpeg shows that you were in the properties of the Form for your screen shot this is where you need to get back to..

You now want to go to the On Current property within this form property screen.
Ie you want to use your mouse to get the cursor within the On Current property at which point a drop down arrow button and another button with dots should appear adjacent to each other to the right hand side of the property line. Using the cursor hit the Button containting the dots. A further window will appear asking whether you wish to access the Expression Builder, Macro Builder or the Code Builder. (you probably realize that Access is flexible and procedures can be run in a number of different ways) I know how to do it in code so I am teaching you in code but all you need to know, at this stage, is that there are a number of different ways to skin a cat.

Right, next you select the code builder and hit ok – you should now be within the VB code sheet – not only that but Access will have created a new procedure for you. Generically automaticlly named something like "Private sub form_current()". This is where you will need to place your SWITCH referred to above.

Your code will almost (but not completely) resemble the text between the double lines.

Right after you have copied the following into your program you will need to alter that part of the text below that is underlined. You will need to change the underlined text to refer to the field name that you have listed within your own database. You have already stated that you have a field in your database that is a tick box that shows whether the form is completed or not. The name of that field is the text that should replace the underlined text.

========
Private Sub Form_Current()

If Me.RecordCompleted = True Then

Me.AllowAdditions = False
Me.AllowDeletions = False
Me.AllowEdits = False

Else

Me.AllowAdditions = True
Me.AllowDeletions = True
Me.AllowEdits = True


End If
End Sub
==========
Save and exit - now any record with the field RecordCompleted set to True(yes) should not be editable or deletable. It should be noted that if you tick the RecordCompleted field while you are within the record, ie you haven't moved out of that records focus you will still be able to edit the record. Once you move off it and if you come back to the record you won't be able to edit it. That's because the switch is run every time you move from one record to the next and until you leave the record the Additions / Edits / Deletions of that record will not be switched.

There are a lot of subtleties to this and you will probably immediately have questions once you've managed to get this working but get this working first and doubtless then you can come back with questions. Gemma is correct but lets assume you just tell your salesmen that they shouldn't be tampering in the background of the database !!!

 

[Lightwave]

Just re-written some of the parts of the text that I felt were weak.

Should highlight that you should not be making changes to the dbase if others are accessing it ,don't worry if you have its just it might crash if you continue doing that.
Normally get people to come out.

Make a backup and work on that firstly would be advisable ..

 

[Sarah]

Hi Guys
just to say this works like a dream.
Thanks for all your help and support.
Sarah

 

[RuralGuy]

Glad you got it sorted Sarah.