none
Ways to lock old data from being editable RRS feed

  • Question

  • Hi

    I have customer in Germany asking me this:

    • Can you confirm that historical data for all finished brews are stored, are not editable anymore and can be reproduced upon request at any time.

    It’s easy so say Yes to "stored data" and the "reproducing". But is there a way to make data not editable? I think the background of this question is demands from Custom and/or Tax authorities.

    My quick fix is to store data monthly in PDF. But is there a way inside a Access database?


    Best // Peter Forss Stockholm GMT +1.00

    Sunday, October 22, 2017 11:12 AM

Answers

  • You might like to take a look at LockForm.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file a form and subform are locked/disabled when not on a new record by the following code, which locks/disables those controls tagged as 'LockMe'.  Unlike setting the AllowEdits property to False this allows unbound controls such as the navigational list box in the demo to remain operational:

        Dim ctrl As Control
        
        ' set caption of cmdEdit button to "Edit Data"
        Me.cmdEdit.Caption = "Edit Data"
        
        ' enable cmdEditButton and move focus ro it
        ' so that other controls can be locked/disabled
        Me.cmdEdit.Visible = True
        Me.cmdEdit.Enabled = True
        Me.cmdEdit.SetFocus
        
        ' loop through controls collection and
        ' lock and disable all contols whose Tag
        ' property is "LockMe"
        For Each ctrl In Me.Controls
            If ctrl.Tag = "LockMe" Then
                ctrl.Enabled = Me.NewRecord
                ctrl.Locked = Not Me.NewRecord
            End If
        Next ctrl
            
        ' if possible move focus to FirstName control and hide
        ' cmdEdit button if at new record
        On Error Resume Next
        Me.FirstName.SetFocus
        Me.cmdEdit.Visible = Not Me.NewRecord
        On Error GoTo 0

    The demo includes the ability to unlock the form for editing via a command button.  In your case this would be omitted of course.  In an operational database it would of course be necessary to prevent editing of the data by other means such as opening a table from the navigation pane.

    Whether this would satisfy the regulatory authorities is another matter.  Access is not a thoroughly secure environment, and never can be.

    Ken Sheridan, Stafford, England

    • Marked as answer by ForssPeterNova Sunday, October 22, 2017 12:12 PM
    Sunday, October 22, 2017 11:39 AM
  • But is there a way inside a Access database?

    Hi Peter,

    I have met this situation in many of my applications. I assume that users have no access to the tables directly, and only work through forms.

    The way I handle this is as follows.

    All overview forms (continuous forms) are not editable, that is: the form itself is editable, but all controls are default locked. It is possible that, depending on certain conditions and authorizations, one or more columns can be unlocked. But in generall all controls are locked.

    To edit a record a single-record form is opened. Depending on certain conditions and authorizations the controls can stay locked or unlocked. For historical financial data e.g. the controls on this single-record form will "always" be locked. The title of the form will be colored according to the editability of this record.

    To add new records, always an separate "New_form" will be used, where (almost) all controls are unlocked, of which a part of the controls MUST get a value, while others are optional. In a few cases some controls stay locked, as they need derived data, as in a postal code environment (postal code - city - street).

    Imb.

    • Marked as answer by ForssPeterNova Sunday, October 22, 2017 12:39 PM
    Sunday, October 22, 2017 12:25 PM

All replies

  • You might like to take a look at LockForm.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file a form and subform are locked/disabled when not on a new record by the following code, which locks/disables those controls tagged as 'LockMe'.  Unlike setting the AllowEdits property to False this allows unbound controls such as the navigational list box in the demo to remain operational:

        Dim ctrl As Control
        
        ' set caption of cmdEdit button to "Edit Data"
        Me.cmdEdit.Caption = "Edit Data"
        
        ' enable cmdEditButton and move focus ro it
        ' so that other controls can be locked/disabled
        Me.cmdEdit.Visible = True
        Me.cmdEdit.Enabled = True
        Me.cmdEdit.SetFocus
        
        ' loop through controls collection and
        ' lock and disable all contols whose Tag
        ' property is "LockMe"
        For Each ctrl In Me.Controls
            If ctrl.Tag = "LockMe" Then
                ctrl.Enabled = Me.NewRecord
                ctrl.Locked = Not Me.NewRecord
            End If
        Next ctrl
            
        ' if possible move focus to FirstName control and hide
        ' cmdEdit button if at new record
        On Error Resume Next
        Me.FirstName.SetFocus
        Me.cmdEdit.Visible = Not Me.NewRecord
        On Error GoTo 0

    The demo includes the ability to unlock the form for editing via a command button.  In your case this would be omitted of course.  In an operational database it would of course be necessary to prevent editing of the data by other means such as opening a table from the navigation pane.

    Whether this would satisfy the regulatory authorities is another matter.  Access is not a thoroughly secure environment, and never can be.

    Ken Sheridan, Stafford, England

    • Marked as answer by ForssPeterNova Sunday, October 22, 2017 12:12 PM
    Sunday, October 22, 2017 11:39 AM
  • Thanks Ken

    I liked what you have done in "LockForm".

    The demands of Customs etc I guess go further. In an Access database the tables is easy to access and of course data then can be editable at any time. It might end up in my PDF-solution. When the brewers are ready then they can create the PDF and send them to Accounting Dept or what ever. If some inspector coming they can "cross read" data in those PDF with the database data and find that no one has cheated.


    Best // Peter Forss Stockholm GMT +1.00


    Sunday, October 22, 2017 12:12 PM
  • But is there a way inside a Access database?

    Hi Peter,

    I have met this situation in many of my applications. I assume that users have no access to the tables directly, and only work through forms.

    The way I handle this is as follows.

    All overview forms (continuous forms) are not editable, that is: the form itself is editable, but all controls are default locked. It is possible that, depending on certain conditions and authorizations, one or more columns can be unlocked. But in generall all controls are locked.

    To edit a record a single-record form is opened. Depending on certain conditions and authorizations the controls can stay locked or unlocked. For historical financial data e.g. the controls on this single-record form will "always" be locked. The title of the form will be colored according to the editability of this record.

    To add new records, always an separate "New_form" will be used, where (almost) all controls are unlocked, of which a part of the controls MUST get a value, while others are optional. In a few cases some controls stay locked, as they need derived data, as in a postal code environment (postal code - city - street).

    Imb.

    • Marked as answer by ForssPeterNova Sunday, October 22, 2017 12:39 PM
    Sunday, October 22, 2017 12:25 PM
  • Thanks Imb

    I have implemented forms where all controls/data fields are locked.

    In the database having my focus right this is hard (for me) to implement when it comes to Brewing Protocols. And they are in the focus of authorities. The Volume brewed and Original gravity is the basis for tax decisions

    One can open a "New Form" for a new record in the database. But since brewers can opertate at least four brews at the same time, sometimes four records must be editable until the brew are done. Some other time there are three or two brews.


    Best // Peter Forss Stockholm GMT +1.00

    Sunday, October 22, 2017 12:39 PM
  • One can open a "New Form" for a new record in the database. But since brewers can opertate at least four brews at the same time, sometimes four records must be editable until the brew are done. Some other time there are three or two brews.

    Hi Peter,

    The situation that I described is the standard way of working, that is implemented automatically when I start a new entity in any application.

    Without having a solution for your problem, I like the brainstorming over new possibilites. In the case of the two, three, four brews at the same time I would use one of my dynamical forms, where a new brew can be added, display 2, 3, 4 brew parallel to each other linked to the right record, finish a brew, etc. A dynamical form is a piece of code where I can define certain properties how controls should behave: an interesting area to explore.

    Imb.

    Sunday, October 22, 2017 2:50 PM
  • But since brewers can opertate at least four brews at the same time, sometimes four records must be editable until the brew are done. Some other time there are three or two brews.
    With the method I posted earlier, you could add a Boolean column, IsLocked say, to the form's table and, having tagged the relevant bound controls, the following function to the form's module:

    PrivateFunction LockForm()

        If Me.IsLocked Then

            Me.SomeControl.SetFocus

            For Each ctrl In Me.Controls
                If ctrl.Tag = "LockMe"
                    ctrl.Enabled = False
                    ctrl.Locked = True
                End If
            Next ctrl

        End If

    End Function

    SomeControl would be any control not being disabled/locked.

    Call the function as the form's Current event property with =LockMe() in the properties sheet, or, if the Current event procedure is already being used, by adding the following line to the procedure:

        LockForm

    Then add a button to the form to set the IsLocked column's value to True, save the current record, and call the above function  with the following in the button's Click event procedure:

        Me.IsLocked = True
        Me.Dirty = False
        LockForm

    Once the brew is completed this button can be pressed to prevent the current record being edited subsequently.  You'd still need to prevent users being able to edit the table by other means of course, but a few minutes googling should find no shortage of advice on ways to do this.


    Ken Sheridan, Stafford, England

    Sunday, October 22, 2017 5:31 PM