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 & AttendanceAppeals"
workdocsPath = "W:Team SpacesCTK 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 & AttendanceAppeals"
workdocsPath = "W:Team SpacesCTK 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 SpacesCTK Absence ManagementUK Health & AttendanceAppealshealth_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 have also tried to comment out the whole function cmdOutcomeLetter_Click() and only have the line “Path = workdocsPath()” inside the function and I get the same error so I suspect this happens when I try to retrieve the path using workdocsPath() and assign it to Path.

This should normally work because IT WORKS WHEN I USE THIS SAME CODE while creating the form, to click this same button. This code works when I use the form for creating the case, but when I use the form to retrieve it from the database, it does not work anymore and I’m unsure why…