Microsoft Access form, "Run-time error '3164': Field cannot be updated." ERROR, Drives me crazy

Question

Found a solution but I would still appreciate help, see the very end of my question!!

I have created a form within access that connects to an outside database, in this form I can create a case for the company I work, with all the required details. It also has some functions to automate some parts of the process, such as a button with function to open a word doc template from a certain folder and modify it with the details from the case. All good so far (I will show you code so you better understand what the form does)

Once I created the second form which looks up in the database for the case number to bring it back and let me update/modify the case, here I have the problem. Once I open a case that was already created, and try to click the button "Create Outcome of Appeal Hearing" to open a word doc from a path location on my disk and automatically update that file with the up to date details from the case, I get this error "Run-time error '3164': Field cannot be updated.".

Here is some code so you get a better idea, the below code is from when I create a case, with the button to create the outcome of appeal hearing document, everything works fine here:

Public Function matchExactCaseNumber(caseNumber As String) As Boolean
If DCount("*", "health_appeals", "exact_case_number='" & caseNumber & "'") > 0 Then
    matchExactCaseNumber = True
Else
    matchExactCaseNumber = False
End If
End Function

Function GetUserFullName() As String
    Dim WSHnet, userName, UserDomain, objUser
    Set WSHnet = CreateObject("WScript.Network")
    userName = WSHnet.userName
    UserDomain = WSHnet.UserDomain
    Set objUser = GetObject("WinNT://" & UserDomain & "/" & userName & ",user")
    GetUserName = objUser.FullName
    Splitusername = Split(GetUserName, ", ")
    SureName = Splitusername(0)
    FirstName = Splitusername(1)
    GetUserFullName = FirstName & " " & SureName
    
End Function

Private Sub Form_Load()
   DoCmd.GoToRecord , , acNewRec
   Me.pxtoc_expert = GetUserFullName()
   Me.pxtoc_expert_mail = Environ("USERNAME") & "@amazon.com"
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim response
    response = MsgBox("Do you want to save this as new record? Are you sure?" & vbCrLf & _
    "Yes=Save new record" & vbCrLf & "No=Close and don't save", vbQuestion + vbYesNo, "Save Changes?")

    If response = vbYes Then
        If matchExactCaseNumber(Me.exact_case_number) = True Then
            MsgBox "This Exact Case number: " & vbCrLf & vbCrLf & _
            Me.exact_case_number & vbCrLf & vbCrLf & _
            "already exists in health_appeals table, please open the update form and update the old case! ", vbInformation
            Me.Undo
            DoCmd.OpenForm "frmHealthAppeals", acNormal
        Else
            MsgBox "New record saved!", vbInformation
            DoCmd.OpenForm "frmHealthAppeals", acNormal
        End If
    ElseIf response = vbNo Then
        'Cancel = False
        Me.Undo
        DoCmd.OpenForm "frmHealthAppeals", acNormal
    Else
        Cancel = True
    End If
End Sub

Public Function workdocsPath() As String
Dim Path As String: Path = "UK Health & Attendance\Appeals\"
workdocsPath = "W:\Team Spaces\CTK Absence Management\" & Path
End Function

Public Function healthFolder() As String

Dim desktopPath As String
Dim folder As String

desktopPath = Environ("USERPROFILE") & "\Desktop\"
folder = desktopPath & "Health&Attendance"

If Dir(folder, vbDirectory) = "" Then
    MkDir folder
    healthFolder = folder
Else
    healthFolder = folder
End If
End Function

Private Sub cmdOutcomeLetter_Click()
    Path = workdocsPath()

    savePath = healthFolder()
    
    Dim wordApp As Object
    Dim wordDoc As Object
    
    Set wordApp = CreateObject("Word.Application")
    
    wordApp.Visible = True
    
    Set wordDoc = wordApp.Documents.Open(Path & "health_appeal_outcome.docx")
    
    With wordDoc
    
    
        .FormFields("today").Result = Format(Date, "dd mmmm yyyy")
        .FormFields("respondent_name").Result = Me.respondent_name
        
    '    ' Home Address 1
    '    If IsNull(Me.home_address_one) Then
    '        .FormFields("home_address_one").Delete
    '    Else
    '        .FormFields("home_address_one").Result = Me.home_address_one
    '    End If
    '    ' Home Address 2
    '    If IsNull(Me.home_address_two) Then
    '        .FormFields("home_address_two").Delete
    '    Else
    '        .FormFields("home_address_two").Result = Me.home_address_two
    '    End If
    '    ' Postcode
    '    If IsNull(Me.postcode) Then
    '        .FormFields("postcode").Delete
    '    Else
    '        .FormFields("postcode").Result = Me.postcode
    '    End If
    '    ' City
    '    If IsNull(Me.city) Then
    '        .FormFields("city").Delete
    '    Else
    '        .FormFields("city").Result = Me.city
    '    End If
    '    ' County
    '    If IsNull(Me.county) Then
    '        .FormFields("county").Delete
    '    Else
    '        .FormFields("county").Result = Me.county
    '    End If
        
        .FormFields("mails").Result = Me.personal_mail_address & ";" & Me.work_mail_address
        .FormFields("respondent_name_two").Result = Me.respondent_name
        .FormFields("meeting_date").Result = Format(Me.meeting_date, "dd mmmm yyyy")
        .FormFields("pxtoc_expert_two").Result = Me.pxtoc_expert
        .FormFields("notetaker_name").Result = Me.notetaker_name
        .FormFields("pxtoc_expert").Result = Me.pxtoc_expert
        .FormFields("respondent_name_thre").Result = Me.respondent_name
    
    End With
    
    
    wordDoc.SaveAs savePath & "\" & Me.respondent_name & " - Disciplinary Appeal Outcome Letter.docx"
    
    
    wordDoc.Activate
    wordDoc.Windows.Application.WindowState = wdWindowStateMinimize
    wordDoc.Windows.Application.WindowState = wdWindowStateMaximize
    
    Set wordApp = Nothing
    Set wordDoc = Nothing
End Sub

Now, all that code was for a form where I am creating a case and have the option to save it in the database. Once I decide to come back to a case and modify it, I use a second form to retrieve it from the database, the code for the form which updates a case is this:

Option Compare Database

Private Sub cmdCloseWindow_Click()
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmFormalHealth", acNormal
End Sub
'_____________________________________________
'_____________________________________________
'__________ Utilities Functions_______________
'_____________________________________________
'_____________________________________________
Public Function workdocsPath() As String
Dim Path As String: Path = "UK Health & Attendance\Appeals\"
workdocsPath = "W:\Team Spaces\CTK Absence Management\" & Path
End Function

Public Function healthFolder() As String

Dim desktopPath As String
Dim folder As String

desktopPath = Environ("USERPROFILE") & "\Desktop\"
folder = desktopPath & "Health&Attendance"

If Dir(folder, vbDirectory) = "" Then
    MkDir folder
    healthFolder = folder
Else
    healthFolder = folder
End If


End Function
Public Function Emailer(ValueCell As String) As String
Dim cellValue As String
Dim valuesArray() As String
Dim i As Long

valuesArray = Split(ValueCell, ",")

For i = LBound(valuesArray) To UBound(valuesArray)
    valuesArray(i) = valuesArray(i) & "@amazon.com"
Next i

Emailer = Join(valuesArray, ";")

End Function

Private Sub cmdOutcomeLetter_Click()
    Path = workdocsPath()

    savePath = healthFolder()
    
   ' Dim wordApp As Object
    'Dim wordDoc As Object
    
    'Set wordApp = CreateObject("Word.Application")
    
    'wordApp.Visible = True
    
    'Set wordDoc = wordApp.Documents.Open(Path & "health_appeal_outcome.docx")
    
    'With wordDoc
    
    
     '   .FormFields("today").Result = Format(Date, "dd mmmm yyyy")
      '  .FormFields("respondent_name").Result = Me.respondent_name
        
    '    ' Home Address 1
    '    If IsNull(Me.home_address_one) Then
    '        .FormFields("home_address_one").Delete
    '    Else
    '        .FormFields("home_address_one").Result = Me.home_address_one
    '    End If
    '    ' Home Address 2
    '    If IsNull(Me.home_address_two) Then
    '        .FormFields("home_address_two").Delete
    '    Else
    '        .FormFields("home_address_two").Result = Me.home_address_two
    '    End If
    '    ' Postcode
    '    If IsNull(Me.postcode) Then
    '        .FormFields("postcode").Delete
    '    Else
    '        .FormFields("postcode").Result = Me.postcode
    '    End If
    '    ' City
    '    If IsNull(Me.city) Then
    '        .FormFields("city").Delete
    '    Else
    '        .FormFields("city").Result = Me.city
    '    End If
    '    ' County
    '    If IsNull(Me.county) Then
    '        .FormFields("county").Delete
    '    Else
    '        .FormFields("county").Result = Me.county
    '    End If
        
       ' .FormFields("mails").Result = Me.personal_mail_address & ";" & Me.work_mail_address
       ' .FormFields("respondent_name_two").Result = Me.respondent_name
       ' .FormFields("meeting_date").Result = Format(Me.meeting_date, "dd mmmm yyyy")
       ' .FormFields("pxtoc_expert_two").Result = Me.pxtoc_expert
       ' .FormFields("notetaker_name").Result = Me.notetaker_name
       ' .FormFields("pxtoc_expert").Result = Me.pxtoc_expert
       ' .FormFields("respondent_name_thre").Result = Me.respondent_name
    
    'End With
    
    
    'wordDoc.SaveAs savePath & "\" & Me.respondent_name & " - Disciplinary Appeal Outcome Letter.docx"
    
    
    'wordDoc.Activate
    'wordDoc.Windows.Application.WindowState = wdWindowStateMinimize
    'wordDoc.Windows.Application.WindowState = wdWindowStateMaximize
    
    'Set wordApp = Nothing
    'Set wordDoc = Nothing
End Sub

As you can see, the functions are identical minus some functions that are not needed inside the form I am updating the fields. Now, when I try to click on the OutcomeLetter and the function cmdOutcomeLetter_Click() runs, I am getting this error at line "Path = workdocsPath()". I have already tested it and the filepath seems to be correct. I just can't understand why is this error occurring.

I have tried to double check the filepath and it seems correct, I have also tried to comment out line "Path = workdocsPath()" where I get this error, and then feed in directly the path of the file to the wordApp.Documents.Open line like so:

Set wordDoc = wordApp.Documents.Open("W:\Team Spaces\CTK Absence Management\UK Health & Attendance\Appeals\health_appeal_outcome.docx")

And surprisingly, this works, but as soon as I use the workdocsPath() function to retrieve the path (which seems to be correct, when it assigns the value of workdocsPath() to Path in line "Path = workdocsPath()", that's when I get the error.

I FOUND out why is this not working, basically when in debugger mode, if I hover over Path variable from line Path = workdocsPath(), it does not have the value of the path retrieved by workdocsPath, I suspect that's why I was getting an error, Path was supposed to be empty, however, it is not so what I did was created another new variable to store the file path and it WORKS.

But now I am intrigued why I cannot reuse the Path variable? Why should I be forced to create a new one? Here is what I did that solved the problem but I still cannot reuse Path for this...:

Dim filePath As String: filePath = workdocsPath()

And so I used the new filePath variable instead. But I should be able to reuse Path, because when I create the case with the first form, I reuse the variable Path with no issues, not sure why with the second form I am unable to reuse it?

Answer 1

Make sure you ALWAYS include Option explicit in all of your code modules.

When you use the variable workdocsPath, that variable will be local to the one routine, and THEN it goes out of scope when you exit that sub/function. However, other code will compile - since you not FORCING that all variables are to be declared. Had you done this, then your code would not have compiled.

So, for all code modules, always, and I repeat ALWAYS have this:

Option Compare Database
Option Explicit

You can have the VBA editor always automatic add "Option Explicit" for you with this option:

From VBA editor -> tools->options, and select this option:

Note that selecting the above option does not change existing modules, but for all new reports/forms, and code modules, then Option Explicit will be automatically added.

Selecting the above means bugs and issues such as your example here would not have occurred.

 Sub MyTest

    sFun = "Hello"

 End Sub


 Sub Mytest2
    
     debug.print sFun

 End Sub

So, in above, when you exit the first sub, then the variable sFun goes out of scope (does not exist). However, since you don't have Option Explicit, then the above code compiles fine. If you turn on option explicit, then the above code will not compile, thus avoiding such errors.