Issues with locking a field in Access Record

Question

I'm having trouble locking a field called "Owner" in my database. The Owner field is populated by grabbing the user id of that user from the network. It's then set through VBA code on a Sub Before Change function to stop it from updating when someone else makes a change to that task (Currently it wants to grab whoever updates the record and override the original owners User ID).

The code is as follows:

Public beforeValueChange As Integer

Private Sub Form_BeforeUpdate(cancel As Integer)
Dim Owner As String
Owner = Environ("USERNAME")

        'If Me.NewRecord = True Then
            Me!Owner = Owner

  On Error Resume Next
  If [Owner] = "Name1" And [User ID] = "Unassigned" Then
  [User ID] = "Name1"

  ElseIf [Owner] = "Name2" And [User ID] = "Unassigned" Then
  [User ID] = "Name2"
  ElseIf [Owner] = "123456" And [User ID] = "Unassigned" Then
  [User ID] = "Name2"

ElseIf [Owner] = Null Then
  [User ID] = "Unassigned"
  End If
End Sub

Public Sub Status_Change()
Dim objOutlookApp As Object
Dim objMail As Object
Dim nConfirmation As Integer

'In progress
    If [Status] = 10 And IsNull(Me.[StartDate]) Then
    [StartDate] = Now()

'Completed
    ElseIf [Status] = 100 And IsNull(Me.[DateCompleted]) Then
    nConfirmation = MsgBox("Are you sure you want to complete this Task?", vbInformation + vbYesNo, "Complete Task?")
        If nConfirmation = vbYes And Not IsNull(Me.[StartDate]) Then
    [DateCompleted] = Now()
        Else
        [Status] = 0
        MsgBox "You cannot complete a task that has not been started"
        End If

'Transferred
    ElseIf [Status] = -10 And IsNull(Me.[DateTransferred]) 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

    'Set Due Date
    If [Priority] = "(1) Hot!" Then
       [DueDate] = Date
    ElseIf [Priority] = "(2) High" Then
       [DueDate] = Date + 1
    ElseIf [Priority] = "(3) Normal" Then
       [DueDate] = Date + 2
    ElseIf [Priority] = "(4) Low" Then
       [DueDate] = Date + 3
    End If

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

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

End Sub

Public Sub Staus_BeforeChange(cancel As Integer)
beforeValueChange = Me.[Status]
End Sub

Public Sub Status_AfterUpdate()
If Not IsNull(Me.[DateCompleted]) Then
Me.[Status] = beforeValueChange
Me.[Status] = 100
End If
End Sub

End Sub
Private Sub Owner_BeforeChange(cancel As Integer)
beforeValueChange = Me.Owner
End Sub

Private Sub Owner_AfterUpdate()
If Not IsNull(Me.Owner) Then
Me.Owner = beforeValueChange
DoCmd.OpenForm "Hal2001"
End Sub

I use something very similar to this to stop changes within a status field and it works just fine. But for some reason I'm having issues with this version on the same form.

EDIT: was hoping not to have to bring in the whole code since it's long but here it is.

EDIT: This is to show that the field is locked and still having the issue

-Deke

What is exact error message? Missing End If line. Use textbox Locked property and VBA only set value if field is null. — June7
Why don't you just set the field to be locked or disabled? Why mess with saving the previous value just to reset it after an edit? Just keep the field from being edited by user in the first place. There is a property called Locked which does exactly what you're attempting to do in code. — C Perkins
@June7 thanks! I've added the end if but I don't even think it's running the code cause I should have gotten an error if the end if was missing. For some reason though it still isn't working. Whoever updates the file is still having their User ID replacing the original. — Deke
@CPerkins I do have the field locked. There is code however, that is pulling the network ID automatically and it keeps overriding the person who last updates that record. — Deke
If the field is locked, then the events you show should never fire because the user cannot change the values from the UI. Those events only fire for changes to the UI, not from within other code. You have not shown the code which actually gets the network ID, so there is not enough info to determine the behavior of the form. — C Perkins

June7 June7 · Accepted Answer · 2019-07-12T19:37:08

Suggest you use variable name different from field or control name, such as strOwner.

Dim strOwner As String
strOwner = Environ("USERNAME")

Try uncommenting line and make a one-liner If Then:

If Me.NewRecord Then Me!Owner = strOwner

or

If IsNull(Me!Owner) Then Me!Owner = strOwner

Or don't bother with variable because it is used only once in procedure.

If Me.NewRecord Then Me!Owner = Environ("USERNAME")

Issues with locking a field in Access Record

1 Answers