Lock Individual Records

[wdrspens]

I have an Access 2007 database that has Fluid records (meaning various fields within them can be changed) and later they become Static records (meaning that thereafter no fields within that record should change).
Is there any way I can lock a record within a table so that the data in the various fields in that record cannot thereafter be changed without first unlocking it, but allowing data in other records to be changed as required.
I need a lock per record - i.e. that applies to all fields in that record, and not per field - i.e. that applies to that field in all records.
Is this possible?
Thanks

 

[DCrake]

Simple Software Solutions

You need to add an extra field to the record and set it to boolean type. give it a meaningful name and set the default value to True

Now each time you visit the record in single form view you can toggle the allow edits option to according to the record status.

 

[wdrspens]

Thank you, and the explanation makes sense, only how do I set the "allow edits" option?
I cannot find any reference to "allow edits" or "allow edits option" in Microsoft Access Help, and so far have found nothing to set such an option.
Many thanks, and please excuse my ignorance.

 

[DCrake]

Simple Software Solutions

Go to the form properties and you will see an option Allow Edits set this to No.

An alternative is to use VBA to set the controls that can't be edited

Me.FieldA.Enabled = False
Me.FieldA.Locked = True

and vice verca to swith the edit mode back on.

This option is the better option as it will allow you be selective on what fields can or cannot be edited.

David

 

[wdrspens]

Thank you very much David, but now that I have tried it I have run up against two problems.

In the Table where I am trying to add another field, I do not get the option to set a Boolean type extra field, only a yes/no check box which, of course, is boolean. I assume therefore you mean such a field.

The real problem lies in the use of the form properties. If I set "Allow Edits" to no, I cannot edit any record at all.

I am no good with VBA and anyway do not want to be able to edit some fields but not others within a record. I either want to be able to edit no fields or all fields, so VBA appears not to be the solution.

Assuming the boolean field name is the original "lock", ought I to put someting like "If (lock = true, no, yes)" in the Allow Edit property? Or what should I put in the Allow Edit property?

Many thanks
David

 

[Rabbie]

I either want to be able to edit no fields or all fields, so VBA appears not to be the solution.

VBA does provide a solution you just lock all fields on the record or allow edits on all the fields depending on what you want. VBA gives you the max possible flexibility.

You would need to use something like Iif(lock = true, no, yes)" in the Allow Edit property

Hope this helps

 

[wdrspens]

I entered Iif(lock = true, no, yes) in the Allow Edit property and got the response "The text you entered isn't an item in the list. Select an item from the list, or enter text that matches one of the listed items." The only choices I have are "yes" or "no"
So thank you very much for your help. I very much appreciate the time you have taken, but do not want to trouble you any further.
David

 

[DCrake]

Simple Software Solutions

Right back to basics.

When you have a current record on the screen you need to decide whether or not the user can edit the record. On your form you place a field from your underlying table/query which it the field that holds the True or False, Yes/No, 0 /-1 indicator as to whether this record can be edited. If the flag is True then use the forms On Current Property and create an events procedure

Private Sub Form_Current()
 Call ToggleControl(Me.Name)
End Sub

Where Me.Name is the name of your form

Sub ToggleControl(frm As Form)
    Dim ctl As Control
    Dim intI As Integer, intCanEdit As Integer
    Const conTransparent = 0
    Const conWhite = 16777215
    For Each ctl in frm.Controls
        With ctl
            Select Case .ControlType
                Case acLabel
                    If .SpecialEffect = acEffectShadow Then
                        .SpecialEffect = acEffectNormal
                        .BorderStyle = conTransparent
                        intCanEdit = True
                    Else
                        .SpecialEffect = acEffectShadow
                        intCanEdit = False
                    End If
                Case acTextBox
                    If .SpecialEffect = acEffectNormal Then
                        .SpecialEffect = acEffectSunken
                        .BackColor = conWhite
                    Else
                        .SpecialEffect = acEffectNormal
                        .BackColor = frm.Detail.BackColor
                    End If
            End Select
        End With
    Next ctl
    If intCanEdit = IFalse Then
        With frm
            .AllowAdditions = False
            .AllowDeletions = False
            .AllowEdits = False
        End With
    Else
        With frm
            .AllowAdditions = True
            .AllowDeletions = True
            .AllowEdits = True
        End With
    End If
End Sub

Add/Remove and code in the above function that suits your needs.

CodeMaster:

 

[wdrspens]

Thank you so very much. It really is exceptionally kind of you and goes far beyond the call of duty.
I will give it a try during the day, today.
Again, thank you so very much.
David

 

[DCrake]

Simple Software Solutions

This forum is renound for getting expert advice when members have the solution to hand. Recognition is also appreciated. Many new users tend to ask for help, get the solution, and we never hear from them again until they hit another problem.

Adding to peoples reputation is a way of saying thankyou. Again glad to be of help.

David

 

[wdrspens]

Oh Dear, I feel a right ninny. Despite reading my (out of date) Complete Reference for Access 2002 book, I cannot find the form name anywhere.
The name in the side bar is [Accounts Transaction List], and I assume it should have the word "Form" appended to it after a period (dot), but that does not work in the VBA designer which continually requires to be debugged.
How do I find the name of the form? It does not appear to be shown in the Properties to the form, and I can find no easy reference to it in my out of date bible.
I have overcome the problem by locking the Accounts Transaction List form completely and designing another form called Edit Transactions Form and specifying which records are displayed within that form (i.e. the records that are not locked). I have added a button to the original form to get to the edit form and vice versa.
I did say I did not like VBA
Again thank you so much for all your help.
David

 

[DCrake]

Simple Software Solutions

When you open a form the first thing access attempts to do is to cache everything is can find out about the form such as its name, the caption, the controls, the rowsource, etc,etc,etc. In most instances it prefixes the entities with the Me. syntax.

To refer to the name of the form use the Me.Name

Otheriwse, as you know the actual name of the form you can use the physical name

Call ToggleControl(Me.Name)

or

Call ToggleControl("Accounts Transaction List")


For easier management of the database it is best not to use spaces in fieldnames or table names. In the case of form names prefix them with Frm so when referring to them you instantly know ou are referring to a form.

Prefixes:

Tables :Tbl
Fields :Fld
Queries:Qry
Forms :Frm
Controls:
Textbox:Txt
Combos:Cbo
Listbox :Lst

Reports:Rpt
Macros:Mcr
Modules:Mdl


David

 

[wdrspens]

Thank you David. It has not worked, but my work-around has resolved the problem, untidily, but since only I use the database, I can put up with that.
Thank you so much for your time and effort. It really is appreciated.
David

 

[shiemstra]

Re: Simple Software Solutions

This forum is renound for getting expert advice when members have the solution to hand. Recognition is also appreciated. Many new users tend to ask for help, get the solution, and we never hear from them again until they hit another problem.

Adding to peoples reputation is a way of saying thankyou. Again glad to be of help.

David

Thanks, David, for the previous post. I am a new Access user/developer and pretty much clue-less. I really don't have the time or resources to delegate administrative or developmental tasks to someone in the know, or even to find such a person. It really is easier to do it myself.

But such would be impossible but for these forums where I can find out how to procede, especially when the posts are as detailed and as "back to the basics (!)" as your very helpful last.

Count me as the one leper in ten that comes back to return thanks.

Thanks again.