Access form Details to Word Template

[stu_c]

Hi all
I have got an access form that a user fills out, once complete I would like the user to click a button that automatically puts the data into a word template with the fields going to the correct places on the word document is this possible? I presume I may have to use bookmarks on word but no idea on the process of this working

 

[NauticalGent]

Try this from Steve Bishop: https://www.youtube.com/watch?v=JilB511V3AU

 

[arnelgp]

create a document in word.eg:

the quick brown fox [1] over the lazy [2].

highlight the 1, including the bracket.
on the word ribbon, Insert->Bookmark.
name your bookmark same as the field in your table where data will come.

do the same with 2, in the example above.
save your doc.

test it:

Private Sub test()
    Dim oWd  As Object 'Word.Application
    Dim oDoc As Object 'Word.Document
    Dim bm   As Object 'Word.Bookmark
    
    'Set oWd = New Word.Application
    Set oWd = CreateObject("Word.Application")
    Set oDoc = oWd.Documents.Open("path where you save the .docx + the filenae")
    
    Set bm = oDoc.Bookmarks("nameOfBookmarkyYouMade")
    bm.Range.Text = "arnelgp"
    oWd.Visible = True
End Sub

 

[stu_c]

Hi guys
Thank you for your help, I have managed to get it to work! just have a few teething issues

1: how can you format the text so its all in CAPS when been copies onto the work documents?

2: I have a field with a start time (24hrs), even when the time has been entered as HH:MM it automatically does it as HH:MM:SS on the word template, how do I default this to HH:MM

 

[Gasman]

Use the UCASE and Format functions as you insert

? ucase("test")
TEST

? format(now(),"hh:nn")
14:03

HTH

 

[stu_c]

Hi guys,
sorry to bring this back up, I got this working great following on from my question above but now wish to try and expand this?, how would I pick up the data from a Tabular sub form (SFRMStaffInvolvements) and put each entry onto a different line within the word document?

I haven't put all the fields in as its basically repeating each step over, form names are
FRMComplaints - Main Form
SFRMStaffInvolvements - Sub Form

Thanks in advance

Private Sub BTNCreateReport_Click()
Dim oWd  As Object 'Word.Application
    Dim oDoc As Object 'Word.Document
    Dim bm   As Object 'Word.Bookmark
   
    'Set oWd = New Word.Application
    Set oWd = CreateObject("Word.Application")
    Set oDoc = oWd.Documents.Add("C:\WORD\TEMPLATES\AccessReport.docx")
   
‘ DATA FROM MAIN FORM
    Set bm = oDoc.Bookmarks("COMPLAINTNO")
    bm.Range.Text = UCase(COMPLAINTNO)
    oWd.Visible = True
   
    Set bm = oDoc.Bookmarks("REPORTINGDATE")
    bm.Range.Text = UCase(REPORTINGDATE)
    oWd.Visible = True

    Set bm = oDoc.Bookmarks("CUSTOMERNAME")
    bm.Range.Text = UCase(CUSTOMERFORENAME & " " & CUSTOMERSURNAME)
    oWd.Visible = True

'---------------------------------------------------------------------------------------------------
‘ DATA FROM SUBFORM
 
Set bm = oDoc.Bookmarks("STAFFINVOLVEMENTS")
  bm.Range.Text = UCase([Forms]![SFRMStaffInvolvements]![STAFFSURNAME])
oWd.Visible = True

End Sub

 

[Gasman]

Check out how The_Doc_Man did it.

Solved - What is the correct way to linearly insert text into Word?

I have searched this site and many others. Web searches galore, but nothing seems to work quite right for me. I am making a document linearly. I.e. providing headers and text and bookmarks and other material in the sequence that I would expect them to be read. But it ain't working. I think...

www.access-programmers.co.uk

 

[The_Doc_Man]

OK, what is the problem? I know your goal, but what happens when you try that would make you seek further help? I need some kind of symptoms to understand better what you tried and what went wrong with it.

 

[stu_c]

Hello,
thanks for the reply, I have tried the following code

bm.Range.Text = UCase([Forms]![FRMComplaints]![SFRMStaffInvolvements].[Form]![STAFFSURNAME])

shows the error message Run-time error '13':
Type mismatch

OK, what is the problem? I know your goal, but what happens when you try that would make you seek further help? I need some kind of symptoms to understand better what you tried and what went wrong with it.

 

[The_Doc_Man]

Can you put a breakpoint in the code on that failing line so that it breaks before you attempt the operation? If so, you can then open the Immediate window (from the menu bar's View option). Type a Debug.Print command twice:

Debug.Print  bm.Range.Text 
Debug.Print  [Forms]![FRMComplaints]![SFRMStaffInvolvements].[Form]![STAFFSURNAME]

You should be able to see what is in each element of the expression. If not, record what it tells you and report it here. My guess is that something is wrong with one of those, so the first step is to find out which one.

 

[stu_c]

I have finally got it to sort of work, it now copies the data onto the word document, the only issue is if I have more than one entry it only shows one of them, not sure if its overwriting the previous input or only reading the last one

 

[The_Doc_Man]

With Word, there is an Options collection that is part of the App properties. You can choose whether you are in INSERT or OVERWRITE mode.

WordApp.Options.ReplaceSelection = False

However, there is also the issue that using bm.Range.Text = syntax is also an implied replace.

You might try something like your first assignment statement, bm.Range.Text = but then follow it with bm.Range.InsertAfter text to append. There might also be issues with formatting there, and it can be difficult. If you just add the text, Word will justify the paragraph. If you add a paragraph marker, that means you have added a paragraph too. That could in some cases be an issue.

Word is really picky on a good day from the GUI. From VBA it can be frustrating beyond belief.

 

[stu_c]

so with regards to WordApp.Options.ReplaceSelection = False , would you put this at the top of all the coding or within each data code for each field eg.

Private Sub BTNCreateReport_Click()
Dim oWd  As Object 'Word.Application
    Dim oDoc As Object 'Word.Document
    Dim bm   As Object 'Word.Bookmark

    'Set oWd = New Word.Application
    WordApp.Options.ReplaceSelection = False
    Set oWd = CreateObject("Word.Application")
    Set oDoc = oWd.Documents.Add("C:\WORD\TEMPLATES\AccessReport.docx")

OR

Private Sub BTNCreateReport_Click()
Dim oWd  As Object 'Word.Application
    Dim oDoc As Object 'Word.Document
    Dim bm   As Object 'Word.Bookmark

    'Set oWd = New Word.Application
    Set oWd = CreateObject("Word.Application")
    Set oDoc = oWd.Documents.Add("C:\WORD\TEMPLATES\AccessReport.docx")
‘ DATA FROM SUBFORM

Set bm = oDoc.Bookmarks("STAFFINVOLVEMENTS")
WordApp.Options.ReplaceSelection = False
bm.Range.Text = UCase([Forms]![SFRMStaffInvolvements]![STAFFSURNAME])
oWd.Visible = True

With Word, there is an Options collection that is part of the App properties. You can choose whether you are in INSERT or OVERWRITE mode.
However, there is also the issue that using bm.Range.Text = syntax is also an implied replace.

You might try something like your first assignment statement, bm.Range.Text = but then follow it with bm.Range.InsertAfter text to append. There might also be issues with formatting there, and it can be difficult. If you just add the text, Word will justify the paragraph. If you add a paragraph marker, that means you have added a paragraph too. That could in some cases be an issue.

Word is really picky on a good day from the GUI. From VBA it can be frustrating beyond belief.

 

[The_Doc_Man]

@stu_c - Setting the .ReplaceSelection option is a one-time thing just after you open the Word App or, at latest, after you open the word document but before you do anything else in it. Note that if you close and re-open the Word app object, you need to reassert your options. However, I believe that closing and re-opening just the document does NOT lose the App.Options settings.

The thing I was saying about "implied replacement" is the syntax of using bm.Range.Text = something. The .Range object would allow you to do .InsertBefore or .InsertAfter or .InsertParagraphBefore, etc. Those are targeted insertions to a reasonably well-defined insertion point (IP). But .Text implies the whole of the .Range unless you acted to constrain it first. Therefore, that equals sign is a replacement, or perhaps an overlay.

Sorry I didn't see this earlier. We are having moderately serious work done on the house and it is barely controlled chaos around here right now. I go for walks just to get away from the noise of saws and drills and hammering.

 

[Pat Hartman]

Forms don't hold data. Tables hold data. Your source to complete this Word document should always be a query that selects the record or records you need. I've attached a sample that includes selecting multiple rows from a child table and placing them into a table in the word document.

 

[stu_c]

sorry for the late reply, been rather manic
problem is there are records in 3 different tables, I have managed to get the information from the main form and the sub forms but my issue is that

Forms don't hold data. Tables hold data. Your source to complete this Word document should always be a query that selects the record or records you need. I've attached a sample that includes selecting multiple rows from a child table and placing them into a table in the word document.

Hello Doc Man
thank you for the below, I genuinely think this has gone past my knowledge of access, are you able to provide any pointers on the actual code?

Private Sub BTNCreateReport_Click()
Dim oWd  As Object 'Word.Application
    Dim oDoc As Object 'Word.Document
    Dim bm   As Object 'Word.Bookmark

    'Set oWd = New Word.Application
    Set oWd = CreateObject("Word.Application")
    Set oDoc = oWd.Documents.Add("C:\WORD\TEMPLATES\AccessReport.docx")
‘ DATA FROM SUBFORM

Set bm = oDoc.Bookmarks("STAFFINVOLVEMENTS")
WordApp.Options.ReplaceSelection = False
bm.Range.Text = UCase([Forms]![SFRMStaffInvolvements]![STAFFSURNAME])
oWd.Visible = True

@stu_c - Setting the .ReplaceSelection option is a one-time thing just after you open the Word App or, at latest, after you open the word document but before you do anything else in it. Note that if you close and re-open the Word app object, you need to reassert your options. However, I believe that closing and re-opening just the document does NOT lose the App.Options settings.

The thing I was saying about "implied replacement" is the syntax of using bm.Range.Text = something. The .Range object would allow you to do .InsertBefore or .InsertAfter or .InsertParagraphBefore, etc. Those are targeted insertions to a reasonably well-defined insertion point (IP). But .Text implies the whole of the .Range unless you acted to constrain it first. Therefore, that equals sign is a replacement, or perhaps an overlay.

Sorry I didn't see this earlier. We are having moderately serious work done on the house and it is barely controlled chaos around here right now. I go for walks just to get away from the noise of saws and drills and hammering.