MS Access Lock a record based on Combo Box?

Question

I'm trying to figure out how to lock a record once I have it set to "Completed" status using a combo box. I have it using a timestamp in a [Date Completed] field and I want to lock it so that field can't be changed again once it has been set. Without this or something like it in place you can keep selecting "In progress" or "Completed" and continually update the time and I'm trying to stop that from happening.

I've tried the following but it locks all the records.

Private Sub Staus_BeforeChange(Cancel As Integer)
'Private Sub Status_Change()
Dim rs As DAO.Recordset
Dim ValueList()


'In progress
If [Status] = 10 Then
[StartDate] = Now()

'Completed
ElseIf [Status] = 100 Then
   [DateCompleted] = Now()

     'Lock record if completed status
        'If Form.CurrentRecord.[Status] = 100 Then
        'Form.CurrentRecord.AllowEdits = False
        'Else
        'Form.CurrentRecord.AllowEdits = True
        'End If

'Transferred
ElseIf [Status] = -10 Then
   [DateTransferred] = Now()
   'Copy Record without completed date
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
    [Status] = 0
    [StartDate] = Null
    [User ID] = "Unassigned"
    [DateTransferred] = Null
    [DueDate] = [DueDate]
MsgBox "The following Task has been transferred:" & " " & Me.[Company] & " " & Me.[Task Name]

'Waiting
ElseIf [Status] = 50 Then
   [DateCompleted] = Now()
End If

Forms![frmTasks].Form.Requery
Forms![frmTasks].Form.Refresh

End Sub


Public beforeValueChange As Integer

beforeValueChange = Me.[Status]

End Sub


Private Sub Status_AfterUpdate()

If Not IsNull(Me.[DateCompleted]) Then

Me.[Status] = beforeValueChange

End If

End Sub

I appreciate any help or push in the right direction. Thanks!!! -Deke

You need to use the Form.Current event to achieve this. This runs each time you load a new record (by moving through the data set). You can then see what values are available in the current record and set the form appropriately. — Martin

Pants Pants · Accepted Answer · 2019-05-23T15:55:33

If that isn't preventing the user from making changes then maybe the below?

Public beforeValueChange As Int


Private Sub Staus_BeforeChange(Cancel As Integer)

beforeValueChange = Me.[Status]

End Sub


Private Sub Status_AfterUpdate()

If Not IsNull(Me.[DateCompleted]) Then

    Me.[Status] = beforeValueChange

End If

End Sub

MS Access Lock a record based on Combo Box?

1 Answers