locked
How to lock fields RRS feed

  • Question

  • I have a subform that allow users to input data in it, i.e., student scores. The main form has some information that links to the subform by studID. Users can go back and modify data, ie., scores in this subform if they want to.

    However, when they close the database, the next time they come back, those records, i.e., scores in the subform should be locked. They can not modify it. In fact, I can lock the fields in the subform by writing some codes in the subfom, but                                                                                                                                                                                                                                                                                                 but it locks right away that the user can not modify the field. Users don't want that; they want they should be able to go back and change data until they close the database. is that possible or isn't because of the Access database capability?

    Wednesday, March 14, 2012 6:22 PM

Answers

  • You can't use the Open event procedure in that way, but in any case using the Current event procedure is enough.  Rather than testing for NULL test for the NewRecord property being True, you can simply examine the form's NewRecord property for True.

    Ken Sheridan, Stafford, England

    • Marked as answer by Bruce Song Wednesday, April 4, 2012 6:33 AM
    Friday, March 16, 2012 6:34 PM

All replies

  • check to see if you are on a new record first.  If you are on a new record, allow changes, if not - lock the fields.  Do this in the "On current" event.

    if me.newrecord then

       'unlock the controls

    else

     'lock the controls

    endif


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Wednesday, March 14, 2012 6:35 PM
  • You'll need some way of indicating which rows have been inserted in the current session and which in an earlier session.  You could add a Session column of long integer data type to the relevant table, and assign a value to a Public variable at start-up with:

    lngSession = Nz(DMax("Session", "YourTable"),0)

    In the subform's BeforeInsert event procedure assign a value to the Session column:

    Me.Session = lngSession + 1

    In the form's Current event procedure unlock the controls if the NewRecord property is True or the Session value is greater than the value of the lngSession variable, else lock the controls

    Ken Sheridan, Stafford, England

    Wednesday, March 14, 2012 7:01 PM
  • Yes, I seem to have missed the second issue of modifying during the current session.  An alternative to the long integer that Ken suggested would be a "freeze time" value into a date/time field rather than an integer field.  That would give you the added benefit of knowing when the edit session occurred.  You can, upon opening the front-end or upon opening the form, save the date/time to a variable.  When you save a record, save the contents of the variable to the date/time field in your table.  Then in the "on current" event you can check to see if it is a new record or if the date/time value matches the date variable and if either of these are true, you can unlock the controls, otherwise you will lock the controls.

    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Wednesday, March 14, 2012 7:33 PM
  • Thank you very much for your advices. I greatly appreciate. As a matter as fact, the way users do in this situation is a little more different than what I described above. Here is the scenario: After a group of poeple (i.e., teachers) enter the scores, only authorized persons can be able to modify scores and lock them when they exit the program to prevent the scores are altered again.

    I plan to create a button on the score subform (described on my post above) once clicked will open a log on form with user name and password, so that only authorized persons (AP) can be able to open the LOCK form. On this LOCK form,  I put the message that prompts AP to mark a check to lock the scores. On the OnClick event of the checkbox, I would write some VBA codes to lock the scores.

    My questions are can I lock the fields programmatically? (These fields should lock the next time should anyone re-open the form to view the scores). And can you please show me codes for this situation? 

    Again thank you for your help.      


    • Edited by ttim Thursday, March 15, 2012 2:53 PM
    Thursday, March 15, 2012 2:43 PM
  • To make the 'lock' persistent you'll need to do as we described and include a column in each row to indicate that it is to be locked.  As this is to be user generated you can make this a Boolean (Yes/No) column IsLocked say, rather than an integer 'Session' or date/time 'FreezeTime' column.  When the user checks the unbound check box you can then update the column to TRUE for all rows with:

    Const conSQL = "UPDATE Yourtable SET IsLocked = TRUE"

    CurrentDb.Execute conSQL, dbFailOnError

    You can make controls in a form read-only by setting their Enabled property to False and their Locked property to True.  This prevents a user moving focus to the control, but leaves the control's appearance unaltered.  To do this from a group of controls n a form first identify the controls to be locked by setting their Tag property to LockMe or something similar.  The code in the subform's Current event procedure to make the controls read-only would then be:

    Dim ctrl As Control
    Dim blnLock As Boolean

    blnLock = Nz(Me.IsLocked,FALSE)

    ' first move focus to another control which
    ' is not to be locked/disabled
    Me.SomeControl.SetFocus

    For Each ctrl In Me.Controls
        If ctrl.Tag = "LockMe" Then
            Me.Enabled = Not blnLock
            Me.Locked = blnLock
        End If
    Next ctrl

    The Nz function is called in the above to unlock the controls when moving to a new record, at which time IsLocked is NULL.

    Ken Sheridan, Stafford, England

    Thursday, March 15, 2012 5:20 PM
  • Thanks, Ken. I think in the score subform, I would write some codes in the Current event and on Open Event, i.e., If Not IsNull() then lock the fields and set Enabled is false. That's it. 

    Then I make a copy of the form including the score subform, change its name and unclock everything. When the authorized poeple need to modify data, they click on the button and enter the password to open this version. And they can do whatever they want.

    Is it OK to do that? Is there any potential dangerous that I am not aware of?

    thanks!

    Friday, March 16, 2012 1:42 PM
  • You can't use the Open event procedure in that way, but in any case using the Current event procedure is enough.  Rather than testing for NULL test for the NewRecord property being True, you can simply examine the form's NewRecord property for True.

    Ken Sheridan, Stafford, England

    • Marked as answer by Bruce Song Wednesday, April 4, 2012 6:33 AM
    Friday, March 16, 2012 6:34 PM