Lock a record once it is 'closed' (1 Viewer)

[gojets1721]

I have a DB of IT tickets and a form which looks at each individually. There's a combo box in the form which allows the user to indicate if the ticket is 'open' or 'closed'.

I was wondering if there's a way to lock all the fields on a record if the combo box is marked as 'closed'. And then the only way to edit any of the data in those fields is if the combo box is switched back to 'open'.

Any suggestions?

 

[The_Doc_Man]

Oh, about a thousand methods.

The simplest method may be to use the form's On_Current event. In that event's code, check your field's status code and if set for Locked (i.e. you want to protect the record), set the form's Me.AllowEdits to FALSE (or if not locked, set it to TRUE).

AllowEdits Property - Microsoft Support

support.microsoft.com

You can also use flags for .DataEntry, .AllowAdditions, or .AllowDeletions to further protect actions according to the status code. AND there are other ways to handle this, but they all start in the On_Current event.

This depends on your users not actually seeing behind the scenes, of course, since if they can get to the navigation pane, all bets are off.

 

[Uncle Gizmo]

Something similar to the following would do I reckon:-

Option Compare Database
Option Explicit

Private Function fLockAll(blnChkStatus As Boolean) As String

    Dim Ctrl As Control
        For Each Ctrl In Me.Controls
            Select Case Ctrl.ControlType
                Case acComboBox, acListBox, acOptionButton, acTextBox, acSubform ', acToggleButton, acSubform , acLabel , acTabCtl, acOptionGroup, acRectangle      ', acCheckBox
                    Ctrl.Enabled = Not blnChkStatus
                    Ctrl.Locked = blnChkStatus
                Case acCommandButton
                    Ctrl.Enabled = Not blnChkStatus
            End Select
        Next Ctrl

        Command8.Enabled = True
        Command9.Enabled = True

End Function      'fLockAll

Private Sub Command8_Click()
    Call fLockAll(True)
End Sub

Private Sub Command9_Click()
    Call fLockAll(False)
End Sub

The above code was derived from the code shown in this Video:-

Lock ALL the Controls on a Form - Nifty Access​

There's a Blog on my website which runs through several different ways of locking and unlocking groups of controls. You might not want to lock all the controls, you might want to lock a particular group. More details here:-

Lock, Unlock Controls​

Lock, Unlock Controls - Nifty Access

Lock, Unlock Controls A problem that crops up from time to time is the need to handle a Group of Controls differently from the rest of the Controls on a Form.Usually this is the need to Lock the Controls so that Data cannot be changed, and conversely Unlock them. However it may be the need

www.niftyaccess.com

 

[gojets1721]

Oh, about a thousand methods.

The simplest method may be to use the form's On_Current event. In that event's code, check your field's status code and if set for Locked (i.e. you want to protect the record), set the form's Me.AllowEdits to FALSE (or if not locked, set it to TRUE).

AllowEdits Property - Microsoft Support

support.microsoft.com

You can also use flags for .DataEntry, .AllowAdditions, or .AllowDeletions to further protect actions according to the status code. AND there are other ways to handle this, but they all start in the On_Current event.

This depends on your users not actually seeing behind the scenes, of course, since if they can get to the navigation pane, all bets are off.

So I tried your first suggestion and its preventing edits. But when I go to a record not marked 'closed', it is locked as well. Below is the code I added to the on current event. Any suggestions?

    If Status = "Closed" Then
        Me.AllowEdits = False
    End If

 

[The_Doc_Man]

    If Status = "Closed" Then
        Me.AllowEdits = False
    Else
        Me.AllowEdits = True
    End If

Remember I suggested "(or if not locked, set it to TRUE)." You failed to do the second half of my suggestion.

You could also have done this:

Me.AllowEdits = ( Status <> "Closed" )

The option here DOES depend on there being only two states - closed or not closed. If there is a third state, this won't work.

 

[KitaYama]

@The_Doc_Man
I don't think your method is appropriate for this case.
Once the ticket is closed and AllowEdits is set to False, how the ticket can be switched back to Open?

This is a one way solution. Open to Close is possible but not vice versa

And then the only way to edit any of the data in those fields is if the combo box is switched back to 'open'.

 

[Pat Hartman]

There really isn't any need to lock the controls. All you need to do is to prevent the update. Therefore, The first test in the form's BeforeUpdate event is to determine the value of the combo.

If Me.cboStatus = "Closed" Then
    Msgbox "You cannot update this record.  It is closed.", vbOKOnly
    Me.Undo
    Cancel = True
    Exit Sub
End If

... the rest of the validation.

You might want validation code in the Status Combo, if you want to control who can change the status.

 

[CJ_London]

@Pat Hartman - how does it work if the user changes the status from open to closed? - think the if statement needs to be

If Me.cboStatus = "Closed" And Me.cboStatus.oldvalue="Closed" Then


that should also cover the situation @KitaYama mentioned if the user wants to reopen the ticket. If that is not allowed then the if statement needs to be

If Me.cboStatus.oldvalue = "Closed" Then

 

[The_Doc_Man]

@The_Doc_Man
I don't think your method is appropriate for this case.
Once the ticket is closed and AllowEdits is set to False, how the ticket can be switched back to Open?

This is a one way solution. Open to Close is possible but not vice versa

Not so. .AllowEdits=False doesn't prevent you from navigating to a different record where you would get a new On_Current event and would therefore get to evaluate the new record's Status field.

 

[KitaYama]

that should also cover the situation @KitaYama mentioned if the user wants to reopen the ticket. If that is not allowed then the if statement needs to be

If Me.cboStatus.oldvalue = "Closed" Then

@CJ_London No, I don't think that will cover it.
The code The_Doc_Man suggests is in the On_Current event of the form. This will prevent the combo box to be updated. It means there's no oldValue. Record is locked and can not be edited.

@The_Doc_Man I'm terribly sorry. But I can't understand what you mean by that.

 

[arnelgp]

i needed 4 events just to make sure to have it Locked/Unlocked.

 

[The_Doc_Man]

@The_Doc_Man I'm terribly sorry. But I can't understand what you mean by that.

When you put code in Form_Current, that code triggers every time you navigate to a different record or save the active record without navigating away from it. Your comment was (I think...) that you didn't see how the form would be unlocked for editing once it became locked. That is the question I am answering. If that is NOT what you meant, please try to ask your question more explicitly.

On the form, when you navigate to a different record, Form_Current event triggers. Inside that event, if you test that locking field, you can set T/F to Me.AllowEdit according to the field. The expression I offered (STATUS <> "CLOSED") will yield either TRUE or FALSE so the expression will provide the correct setting. If status is not "Closed", allow the edits. Here are some cases.

Case 1: The Status field on the newly loaded record is not "Closed" so Me.AllowEdit = TRUE and you can edit the form's fields.

Case 2: The status field was not initially "Closed" (i.e. in case 1) but the user edited the STATUS to "Closed" and then saved the record. After a SAVE operation, Form_Current fires and this time, STATUS = "CLOSED" so the indicated expression will be FALSE. The form will no longer allow editing to occur.

NOTE: There is a timing window between the moment the user changes the status to "CLOSED" and the user saves the record. During that time it would still be possible to un-close the record by editing the Status field again. Once you trigger the SAVE, that ability goes away.

Case 3: The record has been saved. Now the user navigates to a different record. Form_Current fires and tests the status field, which resets Me.AllowEdit as appropriate to the field to which the form just navigated.

Case 4: A new record is created (which implies that Me.AllowAdditions=TRUE). Status will not be "Closed" so edits would be allowed. However, if this is going to happen, you might need to modify that test to Me.AllowEdits = ( NZ( Status, "" ) <> "Closed" ) to offset the null that would likely pop into existence after a new-record navigation.

Case 5: As the result of navigation, a record gets loaded for which Status = "Closed" - and the Form_Current event disables editing. The user cannot edit the form's bound value-oriented controls. However, navigation is not affected. It is possible to leave the record.

Case 6: You previously saved the ticket in a closed state (i.e. case 2 or 5). If you now want to re-open the ticket, add a command button as a separate action that just sets Me.AllowEdits = TRUE and go on about your merry editing way. As part of the _Click event for this putative ReOpen button, you could also change the Status field to "Open" or whatever else is appropriate. Changing the Me.AllowEdits property does not count as an edit since that property isn't visible from Form View, isn't a control, and thus isn't bound. I believe Me.AllowEdits only applies to controls that have values so that command button would do what is required. It won't count as a SAVE action either, since the property isn't bound so would not make anything "dirty" (unless it actually DID change the Status field). Besides that, a command button doesn't have either of the properties .Value OR .OldValue and thus cannot be bound, so wouldn't be affected by the Me.AllowEdits property. Can't edit what doesn't have a value.

 

[Pat Hartman]

how does it work if the user changes the status from open to closed?

That's why I said this:

You might want validation code in the Status Combo, if you want to control who can change the status.

i needed 4 events just to make sure to have it Locked/Unlocked.

I guess you don't believe in my reliance on the form's BeforeUpdate event to prevent the update.

The expression I offered (STATUS <> "CLOSED") will yield either TRUE or FALSE

On a new record, Status will be null in the Current event and so this expression will return null which will be not true which I guess will work.. Personally, I have a strong preference for not using shortcuts like this, partly because if the field can be null (and it is null in the Current event for a NewRecord), you might not get the result you expect. Also, we are humans and humans inherently understand positive statements more clearly than negative ones. The other reason for always testing the positive is because some people are sloppy and the negative might be null. So, old curmudgeon that I am, I use:

If SomeExpression = True Then
    true code
Else
    false code
End If

I also strongly prefer to use a button for the lock/unlock action. That way the field does not need to be unlocked so it can be changed as you are all working with. The field should always be locked and the button should be the only way to control it for simplicity. That also allows you to easily use the AllowEdits property. The AllowEdits is triggered by the click event but you will also need code in the form's current event so you can lock/unlock based on the saved value for new or existing records.

As I said in my initial reply. You have COMPLETE control over whether or nor a record gets updated when you use the form's BeforeUpdate event correctly. It makes absolutely no difference whatsoever if the user changes every control on the form. When the BeforeUpdate event runs, if the Status is not "Open", the code gets rid of all the changes with Me.Undo and cancels the save. This is a one stop shop. You don't need code in multiple events BUT, rather than allow the Status field to be toggled, which requires that the form's AllowEdits be true, I would use a button to solve that problem.

The reason I don't like using the AllowEdits property to lock a form is because most of my forms have some search feature and the AllowEdits prevents even the use of unbound controls (annoying). I have developed other code, for when I need to lock some fields but not others but obviously, in this situation, it would not be necessary since we are talking all or nothing.

 

[gemma-the-husky]

@The_Doc_Man
I don't think your method is appropriate for this case.
Once the ticket is closed and AllowEdits is set to False, how the ticket can be switched back to Open?

This is a one way solution. Open to Close is possible but not vice versa

It's not that. If you navigate to a new record, you need to check the locked flag again.
So the code needs to be in the current event

effectively on a new record.
me.allowedits = (lockedstatus = false)

 

[The_Doc_Man]

So the code needs to be in the current event

But I DID say Form_Current - several times.

 

[KitaYama]

If you now want to re-open the ticket, add a command button as a separate action that just sets Me.AllowEdits = TRUE

With all respects, I know how the events work. The problem was that OP needs to reopen the ticket and you didn't mention about this button. Reading #2 and #5 gives the reader an impression that on_current event does the whole job.
If you add a button to manually set AllowEdits to true then I think the case is solved.

 

[gemma-the-husky]

^^^
Actually, I would set the closed flag back to false, rather than set the locked status to false, if you see what I mean.