troubleshooting Question

Populating a Word Template from Access and Saving as a New Template

Avatar of ynot8669
ynot8669Flag for Australia asked on
Microsoft AccessMicrosoft WordOffice Productivity
9 Comments1 Solution706 ViewsLast Modified:
I have created a couple of word documents with VBA Automation inside them. The user now whats to overlay this with an Access Application that captures client details and also records client activities. That part I am OK with.

I am having issues when they want to assign a Sprinkler Test Report to the client. Access is the main controlling app which launches word as required.

The process is to:
1. open a Master template - blank form
2. Automatically populate the a couple of form fields and Custom Properties (not all have been added to code sample below)
3. Save the document as a client named Master Template into a new Directory
4. Allow the user to populate the remainder of the test form with defaults
5. Close the Client master template when completed (I document form has a save form button that I want to disable at this point - not in code as I done know how to do that).
6. Store the full client master template path and file name back into the database.

The process from here gets easier and I dont think I will have any issues as long as I can get the above field and custom properties updated.

After this the user will recall a client master form, adjust any values and use the save form button --the word vb code will save as a date and timed stamped document into a specific directory and add an activity record to the database.

I have placed a few notes in the code including the error I am getting.

All help gratefully received.

TonyN
Private Sub CreateSiteMastRept_Click()
On Error GoTo EH_CSMR_Click
    
    Dim oApp As Object
    Dim strDoco As String
     
     'strDoco = G_MainPath & "Test Master\-SPRINKLER SYSTEMS.dot"
     strDoco = G_MainPath & "Test Master\-SPRINKLER TEST.dot"
    ' Check the master template is present
    If (isFileExist(strDoco)) = False Then
        MsgBox strDoco, vbCritical, "MASTER REPORT File Does Not Exisit"
        GoTo xit
    End If
    
    'Open the master Tempalte
    Set oApp = CreateObject("Word.Application")
    oApp.Documents.Add Template:=strDoco
  
    'Update template fields and Custom Properties for future refernce in word
    ' ****** This is where I have a problem error 438 - Object Doesn't support property or method
    With oApp
            .Formfields("Site").Result = Me.Organisation_Name
            .CustomDocumentProperties("Client") = Me.Organisation_Name
    End With
    
    'Save the document as an updated template to the Site Directory
     strDoco = G_MainPath & "Test Sites\-" & Trim(Me.Organisation_Name) & "-SPRINKLER SYSTEMS"
     oApp.ActiveDocument.SaveAs FileName:=strDoco, _
        FileFormat:=wdFormatTemplate, LockComments:=False, Password:="", _
        AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
        EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
        :=False, SaveAsAOCELetter:=False
        
    'Allow the user to populate the remainder of the form and save
    oApp.Visible = True
    oApp.Activate
    
    ' SHould I do this or will it create a problem with Normal.dot?
    Set oApp = Nothing ' tidy Up
xit:
Exit_CSMR_Click:
    Exit Sub
 
EH_CSMR_Click:
    Call ErrorLog(Err.Description, Err.Number, Me.Name)
    Resume Exit_CSMR_Click
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros